Sabine said:
I have several worksheets that have between 30 and 50 tabs
and I need to save each tab as a separate file with the
file name referenced in cell a3.
Is it possible to do this automatically?
Try the Sub SaveEachSheetAsBook() below
(adapt the path to suit first - see Notes at bottom)
Steps
-----
Press Alt + F11 to go to VBE
Click Insert > Module
Copy > Paste
everything within the dotted lines below
[from "begin vba" till "end vba"]
into the empty white space on the right side in VBE
---------begin vba----------
Sub SaveEachSheetAsBook()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Call SaveSheet
Next
MsgBox "Processing complete !", _
vbInformation
Application.ScreenUpdating = True
End Sub
Sub SaveSheet()
y = Range("a3").Value
myfilename = "D:\TestFolder\" & y & ".xls"
'change path "D:\TestFolder\" to suit
ActiveSheet.Copy
ActiveWorkbook.SaveAs FileName:=myfilename, _
FileFormat:=xlNormal
ActiveWorkbook.Close True
End Sub
---------end vba----------
Press Alt + Q to exit and return to Excel
-------------
To run the sub*
------------
In Excel (any sheet)
Press Alt + F8
(this brings up the "Macro" dialog)
Click on " SaveEachSheetAsBook " > Run
(or just double click on " SaveEachSheetAsBook ")
-----
*Notes:
-----
Before running the sub above, ensure that the path is
adapted to suit first, i.e. change the
path "D:\TestFolder\" in Sub SaveSheet() to suit
It's assumed that the A3's contents are acceptable as
filenames, i.e. not blank, no illegal characters, etc and
the contents are also not duplicated across the sheets
