Are all the files in one folder?
If yes:
Option Explicit
Sub testme01()
Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk As Workbook
'change the folder here
myPath = "C:\my documents\excel\test\Schedules"
If myPath = "" Then Exit Sub
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop
If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
'the opened file should be the activeworkbook
call DeleteUnused
TempWkbk.Save
TempWkbk.Close savechanges:=False
Next fCtr
End If
End Sub
This goes in the same workbook as your DeleteUnused macro.
Thank you very much for your suggestions
I have created a new workbook with the macro - DeleteUnused. Since I need to
run for over 100 spreadsheets, do you have any suggestions macro coding on
how to open any spreadsheet one by one and apply macro - DeleteUnused on each?
What I intend to do is
First, open the workbook with the macro - DeleteUnused and run the macro
DeleteUnusedAll, which will perform following tasks
open any specific spreadsheet, then apply macro - DeleteUnused, Save & Close
it.
open the next specific sheet 1, then apply ...
...
open the next specific sheet 100 ...
Do you have any suggestions?
Thanks you for any suggestions
Eric
