[Q] Save Sheet Box prompted

[qdeer]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=359#instr


Option Explicit

Sub TwoSheetsAndYourOut()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub

With Application
.ScreenUpdating = False

' Copy specific sheets
' *SET THE SHEET NAMES TO COPY BELOW*
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas
On Error Goto ErrCatcher
Sheets(Array("Copy Me", "Copy Me2")).Copy
On Error Goto 0

' Paste sheets as values
' Remove External Links, Hperlinks and hard-code formulas
' Make sure A1 is selected on all sheets
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select

' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm

' Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False

.ScreenUpdating = True
End With
Exit Sub

ErrCatcher:
MsgBox "Specified sheets do not exist within this workbook"
End Sub



The bold text i highlighted needs to be changed.

I want the path to be prompted so you can choose where you want to save as excel file and name it the way you want it instead of fixing it to the same folder.

Could some1 help me please?

[qdeer]

i tried


set wb= activeworkbook
wb= Application.GetSaveAsFilename (fileFilter:="Excel Files (*.xls), *.xls")


Replacing the bold highlighted text with this.

It does partially what i wanted.

It opens the save box where i want to save it and name it if i want to.

The problem is when i hit save, it opens a new workbook named 'map1' with the sheet i wanted to save, and the workbook isnt saved under the name i wanted. It isnt saved at all.

I dont want it to open the sheet i extracted to a new workbook. I do want it to save under the name i want and to a location of my choice.

Any help would be appreciated!

[Bob Phillips]

You shouldn't hit save, you should save in your code to the filename, which is a string not a workbook object, returned by GetSaveAsFilename.