Run time error '91' object variable or with block not set-CodePudding

Dim bradouts As Workbook

Dim bradsht As Worksheet

If Right(myfile, 4) = ".xls" Then

    Set bradouts = Workbooks.Open(myfolder & myfile)

End If

Set bradsht = bradouts.Activeheet <-----------------------getting error in this line

eRow = bradsht.Range("A" & Rows.Count).End(xlUp).Row

If bradsht.Cells(3, "B") <> "" Then

    bradsht.Range(Cells(3, "B"), Cells(eRow, "L")).Copy

Else

    bradsht.Range(Cells(4, "B"), Cells(eRow, "L")).Copy

End If

CodePudding user response：

You're missing an s in this line:

Set bradsht = bradouts.Activeheet

You probably meant Activesheet (with an s):

Set bradsht = bradouts.Activesheet

CodePudding user response：

The issue is caused because you're only opening the file inside an if statement, and if that if is false, the file isn't opened. That means that bradouts won't get assigned, so you can't use it to get its ActiveWorksheet.

(You have another problem with that if, which is that it won't allow opening .xlsx files, because you only test for a dot and a three letter extension. You may want to fix that, but that's a different question.)

You can fix your current problem by only allowing the code to execute if the if statement is true.

Dim bradouts As Workbook
Dim bradsht As Worksheet

If Right(myfile, 4) = ".xls" Then
  Set bradouts = Workbooks.Open(myfolder & myfile)
  Set bradsht = bradouts.Activeheet 
  eRow = bradsht.Range("A" & Rows.Count).End(xlUp).Row

  If bradsht.Cells(3, "B") <> "" Then
    bradsht.Range(Cells(3, "B"), Cells(eRow, "L")).Copy
  Else
    bradsht.Range(Cells(4, "B"), Cells(eRow, "L")).Copy
  End If
End If