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

Worksheet parsing to a specific folder

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
janthonyn
Asked:
janthonyn
  • 5
  • 2
  • 2
2 Solutions
 
dlmilleCommented:
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
 
Saqib Husain, SyedEngineerCommented:
Dave, should line 16 be

        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\" & nm _
0
 
dlmilleCommented:
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
Technology Partners: 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!

 
dlmilleCommented:
@ssaqibh - are you not getting any sleep these days?  Mines getting better, lately.
0
 
Saqib Husain, SyedEngineerCommented:
You mean sleep is inversely proportional to the points gotten at EE?
0
 
dlmilleCommented:
Could be but....

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

Dave
0
 
janthonynAuthor Commented:
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
 
dlmilleCommented:
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
 
janthonynAuthor Commented:
Many thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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