Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-02-20
7
Medium Priority
?
201 Views
Last Modified: 2014-02-20
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
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39872825
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
 

Author Comment

by:Jagwarman
ID: 39872845
Hi Rgonzo1971

I get Method SaveAs of object_ workbook failed
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39872861
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Jagwarman
ID: 39872922
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
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39872978
From your description not all sheets have a filename at Range("B2")

Is that right?
0
 

Author Comment

by:Jagwarman
ID: 39872983
Ah what an Idiot I am. Brilliant Rgonzo it works perfectly. Thanks
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 39875712
Is your problem solved, Jagwarman? If so, you'll want to accept a comment as the solution, no? :)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question