Ogilvy Help :) - "Object variable or With block variable not set"

[Mike Taylor]

When I execute code below, a Run-time error '91': Object variable or
With block variable not set occurs on the line marked with the ***.
Suspecting the problem is related to the declarations, I
expereiemented with a couple of modifications, but nothing worked. I
am unsure how to correct...any help is greatly appreciated. Kind
Regards, Mike

Sub VisibleTrue()
'code that will programatically loop
'through all the .xls files (each file has 10 worksheets) in a
'directory and

'1) delete the same four worksheets ("Sheet1", "Sheet10", "Sheet3", &
'"Sheet6" from each of the .xls files, and then
'3) change the worksheet Name property of the remaining six worksheets
'so that the worksheet Name property of the first worksheet is
'"Sheet1", the second worksheet is "Sheet2", etc.

Dim basebook As Workbook
'Dim mybook As Workbook
'Dim Item As Worksheet
Dim i As Long, j As Long
Dim mybook As Object
Dim sh As Object
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data\DataFiles\Sept"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Application.DisplayAlerts = False
*** sh.Worksheets(Array("Sheet1", "Sheet2", _
"Sheet3", "Sheet6")).Delete
Application.DisplayAlerts = True
j = 0
For Each sh In Sheets
j = j + 1
sh.Name = j
sh.Visible = True
Next sh
j = 0
For Each sh In Sheets
j = j + 1
sh.Name = "Sheet" & j
Next
mybook.Close SaveChanges:=True
Next i
End If
End With
Application.ScreenUpdating = True
End Sub

 

[Rob Bovey]

Hi Mike,

In this line of code:

*** sh.Worksheets(Array("Sheet1", "Sheet2", _
"Sheet3", "Sheet6")).Delete

you have not set the sh variable. Based on the surrounding context, are you
sure you didn't mean that to be mybook.Worksheets(....).Delete?

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *