Experts Exchange connects you with the people and services you need so you can get back to work.

create a new file for ever tab in workbook and save file with name in cell 'B2'

I need to create a new file for ever tab in workbook and save file with name in cell 'B2'

Would an expert be able to provide the code for doing this

Path is Z:\Flex Rate\Macro

Thanks

Jagwarman

Asked: 2014-02-20

Advertise Here

Microsoft Excel

1 Solution

LVL 55

Rgonzo1971Commented: 2014-02-20

Hi,

pls try

Sub SplitWorkbook()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
            sht.Copy
            ActiveWorkbook.SaveAs Filename:="Z:\Flex Rate\Macro\" & sht.Range("B2") & ".xlsx"
            ActiveWorkbook.Close SaveChanges:=False
    Next sht
End Sub

Open in new window

Regards

JagwarmanAuthor Commented: 2014-02-20

Hi Rgonzo1971

I get Method SaveAs of object_ workbook failed

LVL 55

Rgonzo1971Commented: 2014-02-20

Hi,

What comes in the MsgBox?

Sub SplitWorkbook()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
            sht.Copy
            MsgBox "Filename: " & sht.Range("B2") 
            ActiveWorkbook.SaveAs Filename:="Z:\Flex Rate\Macro\" & sht.Range("B2")
            ActiveWorkbook.Close SaveChanges:=False
    Next sht
End Sub

Open in new window