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

  • Thread starter Thread starter Mike Taylor
  • Start date Start date
M

Mike Taylor

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.
 
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
 
Back
Top