excel saving to xlstart

[rikvny02]

Can someone help. Why are my sheets saving to the XLstart folder instead of the folder path with the original file. Thanks in advance

Private Sub CreateWorkbooks()

Dim WB As Workbook

Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each ws In Worksheets

ws.Copy

Set WB = ActiveWorkbook

WB.SaveAs ThisWorkbook.Path & "\" & ws.Name, FileFormat:=52

WB.Close

Next ws

End Sub

 

[Joe4]

Because "ThisWorkbook" refers to the workbook where the VBA code is stored.
Try this instead:

Private Sub CreateWorkbooks()

Dim WB As Workbook

Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each ws In Worksheets

ws.Copy

Set WB = ActiveWorkbook

WB.SaveAs WB.Path & "\" & ws.Name, FileFormat:=52

WB.Close

Next ws

End Sub

 

[rikvny02]

unfortunately the code fails at:

WB.SaveAs WB.Path & "\" & ws.Name, FileFormat:=52

 

[Joe4]

unfortunately the code fails at:

WB.SaveAs WB.Path & "\" & ws.Name, FileFormat:=52

What is the exact error message you are getting?

Also try adding this line just above that, and tell us what it returns:

MsgBox WB.Path & "\" & ws.Name

 

[Alex Blakenburg]

I have signed of for the night so hopefully @Joe4 will run with it but you are getting the path from the brand new workbook created by the ws.copy which has never been saved before.
You want to capture the path before going into the loop.

 

[rikvny02]

the msg is the name of the 1st sheet



Below is the error message

 

[Joe4]

Did you see Alex's reply?
What directory are you trying to save the file to?
Where does that come from (what relation is it to any of your files)?

 

[rikvny02]

I did not see Alex's reply. The file is saved to a specific folder. H:drive / balances

 

[Joe4]

I did not see Alex's reply. The file is saved to a specific folder. H:drive / balances

So then why not just use:

WB.SaveAs "H:\balances\" & ws.Name, FileFormat:=52

 

[rikvny02]

unfortunately i'm getting the same error. do you think the file could be corrupt?