0

I'm getting this error "range of object workbook" in my code, this just happens when I have another workbook opened and I open the intended workbook

this is the line where it happens

.Range(Cells(1, 1), Cells(lastrow, 2)).Sort Key1:=Range("A1"), _
        Order1:=xlAscending, _
        Header:=xlYes

I don't know what could be happening since it runs smoothly when I don't have any other workbooks opened, even if I just select the intended workbook it runs well, but if i select another workbook it also gets the error

Here's my full code

Option Explicit

Private Sub Workbook_Open()

Dim lastrow As Integer
Dim i As Integer
Dim orig As String
Dim v As Variant
Dim z As Variant
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim exApp As Excel.Application
Dim exwb As Excel.Workbook


Application.EnableEvents = True
'On Error GoTo Exitsub

Set wbBook = Workbooks.Open("link/SHELF LIFE.xlsm")
Set wsSheet = wbBook.Sheets("Shelf Life Data")

Set exApp = CreateObject("Excel.Application")
Workbooks.Open ("link/stability.xlsm")
Set exwb = Workbooks("stability.xlsm")
    
With exwb
    lastrow = .Sheets("SINCE 170522").Cells(Rows.Count, "L").End(xlUp).Row
    v = .Sheets("SINCE 170522").Range(Cells(4, 12), Cells(lastrow, 12)).Value
    z = .Sheets("SINCE 170522").Range(Cells(4, 16), Cells(lastrow, 16)).Value
    .Close
End With

With wsSheet
    .Range("A2").Resize(UBound(v, 1), 1).Value = v
    .Range("B2").Resize(UBound(z, 1), 1).Value = z
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range(Cells(1, 1), Cells(lastrow, 2)).Sort Key1:=Range("A1"), _
        Order1:=xlAscending, _
        Header:=xlYes
        For i = lastrow To 2 Step -1
            If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then
                .Rows(i).EntireRow.Delete
            Else
            End If
        Next i
End With

Exitsub:
End Sub

Any kind of help will be highly appreciated, many thanks

Sosa
  • 135
  • 4

0 Answers0