Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
Anne TroyEast Coast ManagerCommented:
Is your problem solved, Jagwarman? If so, you'll want to accept a comment as the solution, no? :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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