VBA Saveas using a filepath as specified by the user

jamesaplant77

New Member
Joined
Apr 22, 2015
Messages
16
Good afternoon.

I have been going round in circles trying to find the VBA code I need to perform a certain function, however, I have been unable to find it, so I am hoping all you experts will be able to assist me.

I have created a general ledger journal and I want to assign VBA to a button that will allow me to do the following:

1. Will make a copy of the current workbook
2. Will save it in a specified location - using the workbook name as the filename and adding in dd-mm-yyyy & hh-mm-ss to the title, as specified by the user in a msg box
3. Confirmation that the file has been saved successfully in the specified location

I really don't know where to start!"!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
not tested

Code:
sub save_down()
dim book as workbook, work as workbook, sh as worksheet
set work = thisworkbook
set book = workbooks.add
for each sh in work.worksheets
sh.copy book.sheets(book.sheets.count)
next sh
book.saveas(book.path & format(Now,"dd-mm-yyyy hh-mm-ss"))
End Sub
 
Upvote 0
Hi.

It makes a copy of the workbook but I need the workbook title as well as the date and time.

Also, it doesn't give me a message box to type in the filepath where the document needs to be saved!
 
Upvote 0
Code:
Sub save_down()
Dim book As Workbook, work As Workbook, sh As Worksheet
Set work = ThisWorkbook
Set book = Workbooks.Add
For Each sh In work.Worksheets
sh.Copy book.Sheets(book.Sheets.Count)
Next sh
book.SaveAs work.Path & "\" & Left(work.Name, WorksheetFunction.Search(".", work.Name) - 1) & " " & Format(Now, "dd-mm-yyyy hh-mm-ss"), FileFormat:=52
MsgBox " workbook has now saved down"
End Sub
 
Upvote 0
I get the message "workbook has been saved down"

However, I am missing the functionality that should go before this that allows me to enter a filepath of where the document should be saved.

Hoping you can help with this.
 
Upvote 0
Code:
Sub save_down()
Dim book As Workbook, work As Workbook, sh As Worksheet, filepath As String
Set work = ThisWorkbook
Set book = Workbooks.Add
For Each sh In work.Worksheets
sh.Copy book.Sheets(book.Sheets.Count)
Next sh
filepath = InputBox("Please enter in file path")
book.SaveAs filepath & Left(work.Name, WorksheetFunction.Search(".", work.Name) - 1) & " " & Format(Now, "dd-mm-yyyy hh-mm-ss"), FileFormat:=52
MsgBox " workbook has now saved down"
End Sub

path should end with a ""
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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