What code will simply open the 'save as' window?

auto.pilot

auto.pilot

Well-known Member
Joined
Sep 27, 2007
Messages
734
Office Version
  1. 365
Platform
  1. Windows
I'm attempting to write a macro that will copy a range of data and paste it into a new workbook. I think I've figured out the copy/paste part, but workbooks.add does not help me with the new destination. How can I open the 'save as' window, which will cause users to select a path and file name?

thanks in advance

jim
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
From VBA Help

GetSaveAsFilename Method
See AlsoApplies ToExampleSpecificsDisplays the standard Save As dialog box and gets a file name from the user without actually saving any files
 
Upvote 0
Jim1

Jim2 is on the right track, but you need to remember that GetSaveAsFileName does what it says it does.

ie it get's a filename, it doesn't save anything - you'll need further code for that

You'll also need to check if the user hits Cancel.
 
Upvote 0
How about something like this:

Code: 
MsgBox(Prompt:="PLEASE SAVE THE FILE TO A NEW NAME.", _
    Buttons:=vbOKCancel + vbExclamation, Title:="REMINDER")
    'Bring up the Save As screen.
	    If Response = vbOK Then
	    Application.Dialogs(xlDialogSaveAs).Show ("*.xls")
    End If

Harry
 
Upvote 0
And here's the full GetSaveAs method:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> GetSaveAsFileName()<br>    <SPAN style="color:#00007F">Dim</SPAN> FileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Filt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FilterIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#007F00">'   Set to Specified Path\Folder</SPAN><br>        ChDir "C:\Documents and Settings\All Users\Desktop\"<br>    <SPAN style="color:#007F00">'   Set File Filter</SPAN><br>        Filt = "Excel Files (*.xls), *.xls"<br>    <SPAN style="color:#007F00">'   Set *.* to Default</SPAN><br>        FilterIndex = 5<br>    <SPAN style="color:#007F00">'   Set Dialogue Box Caption</SPAN><br>        Title = "Please select a different File"<br>    <SPAN style="color:#007F00">'   Get FileName</SPAN><br>        FileName = Application.GetSaveAsFileName(InitialFileName:=Range("A1"), FileFilter:=Filt, _<br>            FilterIndex:=FilterIndex, Title:=Title)<br>    <SPAN style="color:#007F00">'   Exit if Dialogue box cancelled</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> FileName = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#007F00">'   Display Full Path & File Name</SPAN><br>        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")<br>    <SPAN style="color:#007F00">'   Save & Close Workbook</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook<br>            .SaveAs FileName<br>            .Close<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VBA to Copy Multiple sheets to new workbook & save as .xlsb or .xlsx
2
Replies
16
Views
914
rahildhody
R
willow1985
  • Solved
VBA error handling - File already exists in location
Replies
9
Views
117
NateSC
NateSC
S
  • Question
Saving as dialog box. Application.DisplayAlerts not working
Replies
7
Views
155
Serafin54
S
R
  • Question
VBA Script: Saving worksheet as CSV on MacOS
Replies
1
Views
1K
temphelp
T
M
  • Solved
VBA Code to Break Links
Replies
3
Views
162
MikeVol
MikeVol

Forum statistics

Threads
1,216,060
Messages
6,128,545
Members
449,457
Latest member
ncguzzo

Share this page

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