I want to save it here….. (XL 97–>XP)

Topic

New Reply

Hi All

I have a workbook and I want to save each worksheet as a seperate workbook in a specific network folder. I have written this piece of code, but it defaults to My Documents as expected. How can I get it to save to a network folder without changing the user default option?

Sub Saver()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsName As String

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
ws.Copy

Set wb = ActiveWorkbook

With wb

.SaveAs FileName:=ActiveSheet.Name

End With
Next ws

End Sub

Jerry

Reply | Quote

Replies

Change the line:

.SaveAs FileName:=ActiveSheet.Name

to something like:

.SaveAs FileName:= “H:FolderAFolderB” & ActiveSheet.Name

Reply | Quote

Thanks Brooke

You beat me by two minutes with the answer!!!!

Jerry

Reply | Quote

Hi Brooke

Just a small thing.

Now I have run the code succesfully. The new workbooks remain open, how do I close it each one as and when the code saves it?

Jerry

Reply | Quote

add the line

Activeworkbook.Close

immediately after the .SaveAs line.

Reply | Quote

Thanks, I was thinking Activeworkbook.Close would close the whole workbook not just the current worksheet.

Jerry

Reply | Quote

ActiveWorkbook.Close does close the whole workbook, not just the worksheet. At that point you have two workbooks open. The original workbook and a new one that contains the single worksheet that you copied.

Reply | Quote

Ok Guys

Sorry to waste your time I have solved it.

Sub Saver()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsName As String

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
ws.Copy

Set wb = ActiveWorkbook

With wb

.SaveAs FileName:= (“C:WindowsDesktopMyFolder” & ActiveSheet.Name)

End With
Next ws

End Sub

The path can be changed to wherever you want it

Hope this may be useful to others

Jerry

Reply | Quote