How to direct a macro's output to a particular file folder

[Brad Rego]

Hi all,
I am using the below code to extract worksheets from a given workbook and convert the worksheets into individual workbooks. My question is how do I augment the code to direct the newly created workbooks to a particular folder? Where would such code be inserted?
Thank you very much for feedback!

Sub new_wbs()
Dim i As Integer, s As String, w As String
Application.SheetsInNewWorkbook = 1
Application.ScreenUpdating = False
'
For i = 1 To Sheets.Count
s = ThisWorkbook.Sheets(i).Name
Workbooks.Add
w = ActiveWorkbook.Name
ThisWorkbook.Sheets(i).Copy after:=Workbooks(w).Sheets(1)
Application.DisplayAlerts = False
Workbooks(w).Sheets(1).Delete
Workbooks(w).Sheets(1).Name = s
Workbooks(w).SaveAs Filename:=s & ".xlsx", FileFormat:=51
Workbooks(s & ".xlsx").Close savechanges:=False
Application.DisplayAlerts = True
Next i
'
Application.ScreenUpdating = True
Application.SheetsInNewWorkbook = 3
End Sub

 

[Chihiro]

You can do so by specifying path in SaveAs line. I'd normally store it a variable.

Something like...

Sub new_wbs()
Dim i As Integer, s As String, w As String, sPath As String
Application.SheetsInNewWorkbook = 1
Application.ScreenUpdating = False

'change as needed or store it a cell and reference it
sPath = "C:\Test\"

'
For i = 1 To Sheets.Count
s = ThisWorkbook.Sheets(i).Name
Workbooks.Add
w = ActiveWorkbook.Name
ThisWorkbook.Sheets(i).Copy after:=Workbooks(w).Sheets(1)
Application.DisplayAlerts = False
Workbooks(w).Sheets(1).Delete
Workbooks(w).Sheets(1).Name = s
Workbooks(w).SaveAs Filename:=sPath & s & ".xlsx", FileFormat:=51
Workbooks(s & ".xlsx").Close savechanges:=False
Application.DisplayAlerts = True
Next i
'
Application.ScreenUpdating = True
Application.SheetsInNewWorkbook = 3
End Sub

 

[Brad Rego]

Thank you for the feedback. I added my file path where you have "C:\Test\" but I then get a Run-time error '9' with the following line...

Workbooks(s & ".xlsx").Close savechanges:=False

Any thoughts?
Thank you.

 

[Chihiro]

I can't reproduce the error. Can you upload a sample workbook where you get the error?

 

[Brad Rego]

Attached is a sample workbook. Thank you for your help.

 

[Chihiro]

Ah, you are missing "\" at end of your sPath string. Since you are concatenating sPath with s...

Wrong - "C:\Test" & "Sheet1" = C:\TestSheet1
Good - "C:\Test\" & "Sheet1" = C:\Test\Sheet1