saving tab as file

[sirl212]

Sub SaveTabsAsFile()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentWorksheet As String
Dim CurrentFormat As Long

CurrentWorksheet = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = "G:\Public\"
For Each WS In ThisWorkbook.Worksheets
Sheets(WS.Name).Copy
ActiveWorkbook.SaveAs Filename:=SaveToDirectory, WS.Name, FileFormat:=xlsx
ActiveWorkbook.Close savechanges:=False
ThisWorkbook.Activate
Next

Application.DisplayAlerts = True
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
' about overwriting the original file.

End Sub

:dunno I'm trying to save each tab name on a new workbook as the tab name.xlsx. It works for the first tab and then stops and gives me a syntax error message.

[Kenneth Hobs]

ActiveWorkbook.SaveAs Filename:=SaveToDirectory & WS.Name, FileFormat:=xlsx

[sirl212]

I'm getting "Method 'SaveAs' object'_Workbook' failed, :banghead:

[Kenneth Hobs]

Obviously, you may need to concatenate a file extension if it is not in the worksheet name. Debug by pressing F8 to see each line of code execute. Hover mouse over variables or view the results in Locals Window during debug.

ActiveWorkbook.SaveAs Filename:=SaveToDirectory & WS.Name & ".xlsx", FileFormat:=xlsx