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
janthonynAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
janthonynAuthor Commented:
Many thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.