Clean up multiple Excel Workbooks - Delete all sheets but 1 named 'xxx'

Question

Clean up multiple Excel Workbooks - Delete all sheets but 1 named 'xxx'

Hinks, Daniel 1

I have been looking for a vba code which will clean up all excel files in a specified folder.
By 'clean up', i mean i want it to be able to delete all sheets in the workbook except one named 'xxx', for all workbooks in a target folder.
I have found codes for a single instance where you have the workbook already open, but i was looking for something which would use the FileDialog application to allow the user to specify the folder containing the excel files and apply this action to all of them.

Thanks in advance for your assistance & time. Much appreciated.

Hinks, Daniel 1 Reputation point

2021-02-07T17:29:00.527+00:00

So this is the code i need, except I want it to be applied to all workbooks in one folder...

Sub DeleteSheets1()
Dim xWs As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> "P&C Output" Then
xWs.Delete
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Tom van Stiphout 1,861 Reputation points MVP Volunteer Moderator

2021-02-08T15:25:42.67+00:00

You can loop over the files in a folder using the Dir function - see help file for details.
Hinks, Daniel 1 Reputation point

2021-02-08T18:58:58.727+00:00

Hi,
Thanks for the response. I seem to be getting a Compile error: Argument not optional for the below part of my code:

xbwname = Replace(xStrFile1, ".xls")

Any ideas how to fix this please? My full code is attached. Essentially i am trying to print all work sheets in the excel file names 'P&C Output' to PDF, and for the rest of the sheets to be ignored.65454-module11.txt

1 answer

Your answer

Hinks, Daniel 1 Reputation point

2021-02-07T17:29:00.527+00:00

So this is the code i need, except I want it to be applied to all workbooks in one folder...

Sub DeleteSheets1()
Dim xWs As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> "P&C Output" Then
xWs.Delete
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Tom van Stiphout 1,861 Reputation points MVP Volunteer Moderator

2021-02-08T15:25:42.67+00:00

You can loop over the files in a folder using the Dir function - see help file for details.
Hinks, Daniel 1 Reputation point

2021-02-08T18:58:58.727+00:00

Hi,
Thanks for the response. I seem to be getting a Compile error: Argument not optional for the below part of my code:

xbwname = Replace(xStrFile1, ".xls")

Any ideas how to fix this please? My full code is attached. Essentially i am trying to print all work sheets in the excel file names 'P&C Output' to PDF, and for the rest of the sheets to be ignored.65454-module11.txt

Answer 1

Tom van Stiphout 1,861 MVP Volunteer Moderator

Read the help page on the Replace function. You will learn it takes 3 string arguments, not two.

Share via

Clean up multiple Excel Workbooks - Delete all sheets but 1 named 'xxx'

1 answer

Your answer