VBA Saveas using a filepath as specified by the user

[jamesaplant77]

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!"!

 

[BarryL]

not tested

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

 

[jamesaplant77]

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!

 

[BarryL]

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

 

[jamesaplant77]

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.

 

[BarryL]

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 ""

 

[jamesaplant77]

Awesome, really appreciate all your help BarryL!!!