CODEWITHSUNDEEP
excelvba
user11148708
user11148708

Reputation:

How can I copy the following excel data into a different workbook and have it prompt the user to save?

Essentially I have the following code stating what cells I would like to copy over into another workbook:

Application.CopyObjectsWithCells = False
ActiveSheet.Copy
Range("A1:T40").Copy

Range("A1:T40").PasteSpecial Paste:=xlPasteValues
Application.CopyObjectsWithCells = True

The VBA script automatically opens a new workbook with all of the data pasted from the other workbook.

But now I would also like it to prompt the user with a save window as soon as the new workbook opens, how can this be done?

UPDATE:

The following code prompts the user to save the excel workbook, what can I add to have it save to a specific file with a specific name?

fileSaveName = Application.GetSaveAsFilename( _
 fileFilter:="Excel Workbooks (*.xlsx*),*.xlsx*")
If fileSaveName <> False Then
 MsgBox "Save as " & fileSaveName

End If

UPDATE 2

Dim path As String
Dim filename1 As String


Application.CopyObjectsWithCells = False
ActiveSheet.Copy
Range("A2:T40").Copy

Range("A2:T40").PasteSpecial Paste:=xlPasteValues
Application.CopyObjectsWithCells = True



path = "C:\Users\jmills\Documents\Report\"

filename1 = Range("H1")
Application.DisplayAlerts = True
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook

Application.DisplayAlerts = True


End Sub

How can I keep the save as prompt up? I would like it to allow me to edit the name before it saves

Currently with the code above it saves automatically with whatever is written in cell H1

Upvotes: 0

Views: 93

Answers (1)

Alex de Jong
Alex de Jong

Reputation: 1267

You can trigger the save filename dialog with Application.SaveAsFileName. Here is more info on what you can do with it: https://learn.microsoft.com/en-us/office/vba/api/excel.application.getsaveasfilename

The output of this is a path were you can save the workbook to. So afterwards, you need to use something like ActiveWorkbook.SaveAs Filename:=[output from application.saveasfilename]

Like this:

path = "C:\Users\jmills\Documents\Report\"
fileSaveName = Application.GetSaveAsFilename( initialFilename:=path & "myworkbook", _
 fileFilter:="Excel Workbooks (*.xlsx*),*.xlsx*")
Application.DisplayAlerts = True
ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlOpenXMLWorkbook

Where you can change myworkbook to whatever you want. The user can change that as well in the filedialog if they want.

Upvotes: 1

Related Questions