Saving Worksheet as New WorkBook VBA

[Wes4444]

I have the following code to save a sheet in my current workbook to a new workbook.

Sub Sheet_SaveAs()

Dim wb As Workbook

Sheets("Audit Sheet").Copy

Set wb = ActiveWorkbook

With wb
.SaveAs Format(Date, "yymmdd") & "_REP_" & Range("M4") & "_" & Range("M3") & "_" & Range("AP3")
.Close False
End With

End Sub


This saves the new workbook in the default location of my documents but I want to specify the folder that it goes to.

I am new to VBA so any help will be much appreciated.

 

[FormR]

Hi, welcome to the forum!

I want to specify the folder that it goes to.

Specify how?
-By letting the user choose the folder on the fly?
-By storing the folder in a cell on the worksheet?
-By hardcoding the folder directly in the code?
-Something else?

 

[Wes4444]

Thankyou for such a quick reply!

I don't want the user to be able to choose the folder, I just want the Workbook to be sent to the same folder every time. So if I was able to put the folders location into the code that would be perfect.

 

[FormR]

Hi, then you just simply need to include the path in front of the file name, for example:

Sub Sheet_SaveAs()
 Dim wb As Workbook
 Sheets("Audit Sheet").Copy
 Set wb = ActiveWorkbook
 With wb
  .SaveAs "C:\MyFolder\" & Format(Date, "yymmdd") & "_REP_" & Range("M4") & "_" & Range("M3") & "_" & Range("AP3")
  .Close False
 End With
 End Sub

 

[Wes4444]

Perfect that worked but it also changed the name of the new worksheet by adding the folder name at the begining. Is there a way to do it by keeping the original name without the folder name?

 

[FormR]

but it also changed the name of the new worksheet by adding the folder name at the begining.

Hi, I don't understand - that code doesn't change the name of the worksheet at all.

 

[Wes4444]

My apologies I simply forgot to put in the backslash at the end of the path.

Works perfectly thankyou for your help!

 

[Wes4444]

Sorry one more question. How do I do the same process but save the worksheet as a pdf rather than an excel file?

 

[FormR]

Something along of the lines of:

Sub Sheet_SaveAs()
Sheets("Audit Sheet").ExportAsFixedFormat xlTypePDF, "C:\Folder Name\" & Format(Date, "yymmdd") & "_REP_" & Range("M4") & "_" & Range("M3") & "_" & Range("AP3")
End Sub

 

[Wes4444]

Works a charm thankyou!