Solved

Worksheet parsing to a specific folder

Posted on 2012-03-16
9
277 Views
Last Modified: 2012-03-21
I'm trying to write an Excel sub to copy all the sheets  in a workbook and save them to a specific folder and name them after their tab names. The problem I seem to be having is with saving to a specific folder. The code I have now errors on  Set nm = ActiveSheet.Name.
Sub ParseSheets()

    Dim ws     As Worksheet
    Dim nm     As Name
    Set nm = ActiveSheet.Name
   
    For Each ws In ActiveWorkbook.Worksheets
        ws.Copy
        'ActiveWorkbook.SaveAs (ActiveSheet.Name)
        ActiveWorkbook.SaveAs Filename:= _
        "\\domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\& nm" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
       
       
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
End Sub
0
Comment
Question by:janthonyn
  • 5
  • 2
  • 2
9 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37731489
That's because you declared nm as name.  It should be declared as String type

Also, you have nm in quotations on the save, and it should not be.

PS - I recommend using MOVE rather than copy.   That's because if you have any cross-sheet links, you'll have problems with their linkages (they will link back to the workbook you're copying from and if that's what you want, then its fine).

See article on the subject:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_9730-Workbook-link-problems-after-copying-tabs-to-a-new-workbook.html

Please vote YES if you think its helpful.

My suggested coding, which I just tested successfully:
Sub ParseSheets()

    Dim wkb As Workbook
    Dim ws     As Worksheet
    Dim nm     As String
    
    
    nm = ActiveSheet.Name
    
    Set wkb = ActiveWorkbook
    Application.DisplayAlerts = False
    
    For Each ws In wkb.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\& nm" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
End Sub

Open in new window

Dave
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 20 total points
ID: 37731609
Dave, should line 16 be

        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\" & nm _
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37731611
Yes I noted that but my change didn't seem to take

Sub ParseSheets()

    Dim wkb As Workbook
    Dim ws     As Worksheet
    Dim nm     As String
    
    
    nm = ActiveSheet.Name
    
    Set wkb = ActiveWorkbook
    Application.DisplayAlerts = False
    
    For Each ws In wkb.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\" & nm _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
End Sub

Open in new window


Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37731637
@ssaqibh - are you not getting any sleep these days?  Mines getting better, lately.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37731649
You mean sleep is inversely proportional to the points gotten at EE?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37731651
Could be but....

Nope - I just notice you round the clock and was concerned you weren't getting sleep or sun ;)

Dave
0
 

Author Comment

by:janthonyn
ID: 37737991
Thank you both very much for your help with this one. I tested this solution, but did not get good results. The sub runs through one time and saves the first tab in the correct folder but it is named "Sheet1". After that the sub exits and doesn't parse or save any other sheets. I opened Sheet1.xls and the tab is named "DATA." and doesn't include any of the data or structure of any of the tabs in the original workbook.  I'm at a loss for why this didn't work.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 480 total points
ID: 37738530
Actually, I was just repairing your syntax errors, not looking at your logic, as I thought that was the help you were looking for.

However, I see the problem in that your file name is set only once and its outside your loop.  Try this, and review the changes I've made before you run it to ensure you understand what I did.

Sub ParseSheets()

    Dim wkb As Workbook
    Dim ws     As Worksheet
    Dim nm     As String
    
    
    Set wkb = ActiveWorkbook
    Application.DisplayAlerts = False
    
    For Each ws In wkb.Worksheets
        nm = ws.Name
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\" & nm _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
End Sub

Open in new window


Dave
0
 

Author Closing Comment

by:janthonyn
ID: 37747870
Many thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now