Save Worksheets as Tab Name plus Cell Value and Date

[SuperFerret]

Hello,

I want to be able to save worksheets in my workbook as individual workbooks, using the tab name, a cell value and the date (DD MMM Format)

Basically, I have a workbook which we are using to upload to a new system, and we have devised a workbook where only one worksheet has to be completed and the other worksheets are filled in ready.

The problem is I have to have each of these sheets as individual workbooks to load into the system which is very time consuming.

I can't work out how to split the workbook into the 5 sheets, and rename them (should be saved as TABNAME_cellA2value_DD_MMM.xls)

Any help would be appreciated!

 

[Yahya]

if your Excel is 2007 or 2010
try this

Sub Test()
Dim ws As Worksheet, wb As Workbook
Application.DisplayAlerts = False
For Each ws In Sheets
    If ws.Name <> "DATA" Then
        Workbooks.Add
        Set wb = ActiveWorkbook
        With wb
            .SaveAs Filename:="S:\CREATES\" & ws.Name & "_" & ws.Range("A2").Value & "_" & Format(Date, "DD MMM") & ".xlsx"
            ws.Copy Before:=.Sheets(1)
            .Sheets(1).Cells.Copy
            .Sheets(1).Cells.PasteSpecial xlValues
            .Save
            .Close
        End With
    End If
Next ws
Application.DisplayAlerts = True
End Sub

 

[SuperFerret]

I need it in .xls as the system won't accept an xlsx, I added FileFormat:=56 to it and it works perfectly!

Thanks for all your help Yahya you're a star!

 

[Yahya]

I need it in .xls as the system won't accept an xlsx, I added FileFormat:=56 to it and it works perfectly!

Thanks for all your help Yahya you're a star!

you are very welcome
but the real stars the MVP's of MrExcel board

 

[SuperFerret]

I'm coming up against a snag. Basically they've decided to shift the folders around (why I don't know) and now there I need to change part of the file ref:

From:
S:\Creates\ME CREATIONS\

To:
S:\Creates\(Cell DATA!Z2) CREATES\

But I don't know how to add this into the code?