Solved: Saving a copy of a spreadsheet, external file?

[Hoopsah]

Hi,

I have attached a copy of a program I hope to utilise, as you will see it has a lot of macro buttons that after the user has used the drop down menus it works out a price - blah blah blah.

That bit all works ok, so not too bothered about this part.

However........................

I would like to add a button that after everything has been input and viewed, the user can then save.

Now I would only want to save the page/tab that the user is on (Est Cost Worksheet) and if possible to ask the user to select the path inwhich to save it.

Hope that makes sense, any feedback would be appreciated,

Cheers

Hoopsah

[lucas]

Try this Hoopsah:
Option Explicit
Dim FName As Variant
Sub SaveCopyOfEstCostWorksheet()
Sheets("Est Cost Worksheet").Select
Sheets("Est Cost Worksheet").Copy
FName = Application.GetSaveAsFilename(InitialFileName:="Est Cost Worksheet")
If FName = False Then
'Cancel
Else
ActiveWorkbook.SaveAs FName & "xls"
ActiveWorkbook.Close False
Sheets("Notes").Select
End If
End Sub

[lucas]

This is better....the other was hasty. This gets rid of the select and closes the copy if they cancel.
Option Explicit
Sub SaveCopyOfWorksheet()
Dim FName
Sheets("Est Cost Worksheet").Copy
FName = Application.GetSaveAsFilename(InitialFileName:="anynameyouwant")
If FName = False Then
ActiveWorkbook.Close False
Else
ActiveWorkbook.SaveAs FName & "xls"
ActiveWorkbook.Close False
End If
End Sub

[Hoopsah]

Hi Lucas,

That works really well, thank you :thumb

One last thing, when it brings up the Save As box, is there anyway that I can input a path so that it will bring up a particular directory?

This workbook will be going out to different regional teams and I would like them to be able to save it in their specific folder.

Cheers

Hoopsah

[mdmackillop]

ChDir "C:\Check" '<--Change to suit
FName = Application.GetSaveAsFilename(InitialFileName:="anynameyouwant")

[Hoopsah]

Absolutely Fantastic.

This is great, thanks a lot guys it works perfectly.

Cheers again

P.S. I am marking this as solved.