Save tabs in new worksheets depending on name of tabs

[CRMTHB]

Hi,

I have multiple tabs on a worksheet, and I want each individual tab to be saved in a location depending on the name of the tab. The actual name of the file would be the tab name, plus the contents of cell A1.

So for example, if the tab is called "Bananas" and has "1234" in cell A1, the tab is to be saved as a new worksheet called "Bananas 1234" in location Q:\Departments\XX\ZZ\Bananas.

Is this possible? Any help would be appreciated

 

[mumps]

Try:

Sub SaveSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:="Q:\Departments\XX\ZZ\" & ActiveSheet.Name & "\" & ActiveSheet.Name & ActiveSheet.Range("A1").Value & ".xlsx"
        ActiveWorkbook.Close False
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[CRMTHB]

Thanks mumps, that is working well, although it does fail if there no folder exists with that name.

I assume it is not possible for an excel macro to create a new folder?

 

[Fluff]

How about

Sub crmthb()
   Dim Ws As Worksheet
   Dim Pth As String
   
   For Each Ws In Worksheets
       Pth = "Q:\Departments\XX\ZZ\" & Ws.Name & "\"
       If Dir(Pth, vbDirectory) = "" Then MkDir Pth
       Ws.Copy
       ActiveWorkbook.SaveAs Pth & ActiveSheet.Name & ActiveSheet.Range("A1").Value & ".xlsx", 51
       ActiveWorkbook.Close False
   Next Ws
End Sub