troubleshooting Question

Worksheet parsing to a specific folder

Avatar of janthonyn
janthonyn asked on
Visual Basic ClassicMicrosoft Excel
9 Comments2 Solutions304 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros