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




"Ormond" wrote in message
...
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