I have the following macro which opens all the Excel files within a given folder, loops through them all and deletes columns G to N, then it re-saves each of the files.
It runs without error, but when I opened the Excel files to check that the columns had been deleted correctly, I noticed that the macro had only worked for one tab per file (Sheet1). However, I need it to loop through all the tabs in each of the files to delete the unwanted columns. The tab names are not the same in all the spreadsheet files.
I thought that the For Each Worksheet In ActiveWorkbook.Worksheets command would loop through all of the tabs within each of the open files?
How would I fix the macro so that it works for every tab?
Sub OpenandchangeAllFiles()
Dim fList() As String
Dim fName As String
Dim fPath As String
Dim I As Integer
'This folder will be searched for Excel files
fPath = "C:\My Documents"
'build a list of the files
fName = Dir(fPath & "*.xls")
While fName <> ""
'add fName To the list
I = I + 1
ReDim Preserve fList(1 To I)
fList(I) = fName
'get Next filename
fName = Dir()
Wend
'see If any files were found
If I = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list And Open
For I = 1 To UBound(fList)
Workbooks.Open fPath & fList(I)
'select all worksheets In turn
For Each Worksheet In ActiveWorkbook.Worksheets
'delete all unwanted columns
Columns("G:N").Select
Selection.Delete Shift:=xlToLeft
Next 'next worksheet
'close & save changes To workbook
ActiveWorkbook.Close True
Next 'next workbook
MsgBox "All unwanted columns have now been deleted from all the spreadsheets."
End Sub
Bookmarks