asked on
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
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False
ActiveWorkbook.Close SaveChanges:=True
Next ws
End Sub
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
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False
ActiveWorkbook.Close SaveChanges:=True
Next ws
End Sub
Microsoft ExcelVisual Basic Classic
Last Comment
janthonyn
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Yes I noted that but my change didn't seem to take
Dave
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
Dave
@ssaqibh - are you not getting any sleep these days? Mines getting better, lately.
You mean sleep is inversely proportional to the points gotten at EE?
Could be but....
Nope - I just notice you round the clock and was concerned you weren't getting sleep or sun ;)
Dave
Nope - I just notice you round the clock and was concerned you weren't getting sleep or sun ;)
Dave
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Many thanks.
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:
https://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:
Open in new window
Dave