• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 3
  • 3
1 Solution
 
Rgonzo1971Commented:
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
0
 
JagwarmanAuthor Commented:
Hi Rgonzo1971

I get Method SaveAs of object_ workbook failed
0
 
Rgonzo1971Commented:
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JagwarmanAuthor Commented:
First time round it saves the shet named Data Sheet and gives me name of next sheet. Next time MsgBox is blank and then it falls over
0
 
Rgonzo1971Commented:
From your description not all sheets have a filename at Range("B2")

Is that right?
0
 
JagwarmanAuthor Commented:
Ah what an Idiot I am. Brilliant Rgonzo it works perfectly. Thanks
0
 
DreamboatCommented:
Is your problem solved, Jagwarman? If so, you'll want to accept a comment as the solution, no? :)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now