Loop thru files in DIR, delete worksheets & reset worksheet Name property??
 

Can anyone share idea(s) for 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.

Any ideas are greatly appreciated.

Loop thru files in DIR, delete worksheets & reset worksheet Name property??
 

Option Explicit
Sub VisibleTrue()
Dim basebook As Workbook
Dim mybook As Workbook
'Dim Item As Worksheet
Dim i As Long, j as Long
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","Sheet3", _
"Sheet6", "Sheet10")).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
Application.ScreenUpdating = True
End Sub


--
Regards,
Tom Ogilvy

Mike Taylor wrote in message
om...
Can anyone share idea(s) for 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.

Any ideas are greatly appreciated.