Saving Worksheet as New WorkBook VBA

Wes4444

New Member
Joined
Dec 7, 2017
Messages
21
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
Hi, then you just simply need to include the path in front of the file name, for example:

Rich (BB code):
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
My apologies I simply forgot to put in the backslash at the end of the path.

Works perfectly thankyou for your help!
 
Upvote 0
Sorry one more question. How do I do the same process but save the worksheet as a pdf rather than an excel file?
 
Upvote 0
Something along of the lines of:

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

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top