Save one sheet?

Posted by Jennie the confused on June 29, 2001 9:00 AM

I think I need the VBA'ers help with this. I can add a command button. But what code can I use to save only 1 sheet of a 18 sheet workbook? I want to prompt the user for the name of the new file. Thanks guys!

Posted by Ben O. on June 29, 2001 9:34 AM

This code should work. If firsts copies the sheet to a new workbook, then prompts the user for a file name, then saves the workbook unless the user hits cancel.

Change "Sheet1" to the name of the sheet you want saved. You can also add some parameters to the Application.GetSaveAsFile statement if you want to set the initial directory or other options for the save.

Sub SaveSheet()
Sheets("Sheet1").Copy
safName = Application.GetSaveAsFilename
If safName &LT;&GT; False Then
ActiveWorkbook.SaveAs Filename:=safName, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
Else
Exit Sub
End If
End Sub

-Ben

Posted by Jennie the confused on June 29, 2001 11:25 AM

Thanks......can we expand on this a little??

Thanks Ben! I tried it and it worked. But is it possible to save that sheet, close the newly created file, and return to the original workbook? Any more advice would be wonderful :))

Posted by Ben O. on June 29, 2001 1:30 PM

Re: Thanks......can we expand on this a little??

Jennie,

Inserting an ActiveWorkbook.Close statement before "End If" will accomplish what you want.

-Ben Thanks Ben! I tried it and it worked. But is it possible to save that sheet, close the newly created file, and return to the original workbook? Any more advice would be wonderful :))

Posted by Jennie - not as confused on June 29, 2001 2:45 PM

Thanks Ben....it worked!

Inserting an ActiveWorkbook.Close statement before "End If" will accomplish what you want. : Thanks Ben! I tried it and it worked. But is it possible to save that sheet, close the newly created file, and return to the original workbook? Any more advice would be wonderful :)) :