"Run-time error '91': Object variable or With block variable not set" error

[sspatriots]

Hi,

I get the "Run-time error '91': Object variable or With block variable not set" error when I run my code below". The error occurs at the line directly below.

With wbCurUsers.Worksheets(1)

Any help would be much appreciated. Thank you, SS


Code I'm trying to run:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wbCurUsers As Workbook
    Dim strUser As String
   
    strUser = Environ("UserName")
   
    Set wbCurUsers = Workbooks.Open(Filename:=ThisWorkbook.Path & "\Current Users.xlsx")
   
    With wbCurUsers.Worksheets(1)
        .Columns(1).Replace What:=strUser, Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
   
    wbCurUsers.Close SaveChanges:=True
    
End Sub

 

[Micron]

Looks good; works for me (up to loading one of my own wb's). I tried with an invalid file reference but can't suppress the error message (can't find...) so I suppose you have not done that since you're not getting that error. You're not using On Error Resume Next either, so not that. Maybe try another sheet reference (e.g. by name, not by index). Or is your sheet a chart sheet? AFAIK, a chart is a sheet but it's not a worksheet.

If none of that helps, maybe try Sheets reference - wbCurUsers.Sheets("SheetNameHere")

 

[sspatriots]

Looks good; works for me (up to loading one of my own wb's). I tried with an invalid file reference but can't suppress the error message (can't find...) so I suppose you have not done that since you're not getting that error. You're not using On Error Resume Next either, so not that. Maybe try another sheet reference (e.g. by name, not by index). Or is your sheet a chart sheet? AFAIK, a chart is a sheet but it's not a worksheet.

If none of that helps, maybe try Sheets reference - wbCurUsers.Sheets("SheetNameHere")

I have noticed that it only happens when I use Power Automate to launch the workbook, do something, then save and close.

 

[Micron]

Don't know what PA is, sorry. Only other suggestion I have is to step through the code. You can close the workbook to call the code but you'd need a breakpoint so it doesn't just close on you, or put a Stop statement before your problem line. As I see it, you don't know if the problem is that the wb object is invalid or if it's the sheet reference. If stepping through and examining what's going on doesn't help (you can use immediate window to refer to variables whose values have been set) then you could try Dim sht As Worksheet. Set the wb object as before, but then try to Set the sht object to Worksheets(1). Or use the other syntax as I've suggested. I still don't know if the reference is a chart or not (probably not).

EDIT - it's also possible to call the sub from the immediate window, but simpler to do as suggested, I think.

 

[sspatriots]

Don't know what PA is, sorry. Only other suggestion I have is to step through the code. You can close the workbook to call the code but you'd need a breakpoint so it doesn't just close on you, or put a Stop statement before your problem line. As I see it, you don't know if the problem is that the wb object is invalid or if it's the sheet reference. If stepping through and examining what's going on doesn't help (you can use immediate window to refer to variables whose values have been set) then you could try Dim sht As Worksheet. Set the wb object as before, but then try to Set the sht object to Worksheets(1). Or use the other syntax as I've suggested. I still don't know if the reference is a chart or not (probably not).

EDIT - it's also possible to call the sub from the immediate window, but simpler to do as suggested, I think.

I will give that a try. Thanks.

 

[Micron]

then try to Set the sht object to Worksheets(1)

Thought perhaps I should clarify that it would be like so:

Dim wbCurUsers As Workbook
Dim sht As Worksheet
Dim strUser As String

Set wbCurUsers = Workbooks.Open(Filename:=ThisWorkbook.Path & "\Current Users.xlsx")

'this may error and reveal this reference to be the issue
Set sht = wbCurUsers.Worksheets(1) 
'if not I'd add this to test. If the sheet name is displayed then I'm running out of ideas
msgbox sht.Name