I'd like to make a command button that will export a specific worksheet as a
..csv file. Upon clicking the button, I'd like a prompt for the file name,
then once I've saved the file I'd like to be back on the worksheet where I
started.

I've pasted together some code from other questions and I've almost got what
I want, but it doesn't quite work. The code below will save the worksheet as
a .csv file. But the new file remains open and active. I'd like the new
file to be closed after I save it and I'd like to be back at the worksheet
where I started in the original workbook. I'd appreciate any suggestions

Private Sub CommandButton1_Click()
Sheets("MySheet").Select
Sheets("MySheet").Copy
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName, _
FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
End Sub

Where "MySheet" is the sheet that gets saved as a .csv file.
ReadOnlyRecommended:=False, CreateBackup:=False

Thanks. I added the following code to get me back to the worksheet that I
started at:

Worksheets("YourSpecialSheetName").Activate

I've added a few extra keywords so this might help other people searching
for answers
KEYWORDS: SaveAs Method, text file, comma delimited file,

"Nigel" wrote:

> At the end of your code add this........
>
> ActiveWorkbook.Close savechanges:=False
>
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "Ormond" <(E-Mail Removed)> wrote in message
> news:5EE5D581-F328-4C85-B411-(E-Mail Removed)...
> >I made a mistake on the copy/paste of the code... the code that I have is
> >as
> > follows:
> >
> > Private Sub CommandButton1_Click()
> > Sheets("MySheet").Select
> > Sheets("MySheet").Copy
> > Do
> > fName = Application.GetSaveAsFilename
> > Loop Until fName <> False
> > ActiveWorkbook.SaveAs Filename:=fName, _
> > FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
> > ReadOnlyRecommended:=False, CreateBackup:=False
> > End Sub
> >
> > Where "MySheet" is the worksheet that gets saved to a .csv file
> >
> > "Ormond" wrote:
> >
> >> I'd like to make a command button that will export a specific worksheet
> >> as a
> >> .csv file. Upon clicking the button, I'd like a prompt for the file
> >> name,
> >> then once I've saved the file I'd like to be back on the worksheet where
> >> I
> >> started.
> >>
> >> I've pasted together some code from other questions and I've almost got
> >> what
> >> I want, but it doesn't quite work. The code below will save the
> >> worksheet as
> >> a .csv file. But the new file remains open and active. I'd like the new
> >> file to be closed after I save it and I'd like to be back at the
> >> worksheet
> >> where I started in the original workbook. I'd appreciate any suggestions
> >>
> >> Private Sub CommandButton1_Click()
> >> Sheets("MySheet").Select
> >> Sheets("MySheet").Copy
> >> Do
> >> fName = Application.GetSaveAsFilename
> >> Loop Until fName <> False
> >> ActiveWorkbook.SaveAs Filename:=fName, _
> >> FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
> >> End Sub
> >>
> >> Where "MySheet" is the sheet that gets saved as a .csv file.
> >> ReadOnlyRecommended:=False, CreateBackup:=False
>
>