Archive of Mr Excel Message Board


Back to Controls in Excel archive index
Back to archive home

Saving a sheet through code.

Posted by RJ on June 13, 2001 3:59 PM
Seen similar posts, but now answers. What I'm trying to do is place a command button with code that will save 1 sheet from my workbook to a new file, close that file, and return to my open workbook. Sounds similar to what Dave asked below. I think we're looking for the same solution perhaps. Dave, if I get an answer I'll send it your way. Thanks VBA'ers

Re: Saving a sheet through code.

Posted by Chuck on June 13, 2001 5:10 PM

See if this helps.

Sub Macro2()
ActiveWorkbook.Save
ActiveWorkbook.SaveAs_ FileName:="C:\excel\Book2.xls"
Workbooks.Open FileName:="C:\Excel\Book1.xls"
Workbooks("BOOK2.XLS").Close SaveChanges:=True
End Sub


Re: Saving a sheet through code.

Posted by Chuck on June 13, 2001 5:10 PM

See if this helps.

Sub Macro2()
ActiveWorkbook.Save
ActiveWorkbook.SaveAs_ FileName:="C:\excel\Book2.xls"
Workbooks.Open FileName:="C:\Excel\Book1.xls"
Workbooks("BOOK2.XLS").Close SaveChanges:=True
End Sub


Thx....but looking to save 1 sheet from the workbook.

Posted by RJ on June 14, 2001 8:31 AM
As_ FileName:="C:\excel\Book2.xls"

: Seen similar posts, but now answers. What I'm trying to do is place a command button with code that will save 1 sheet from my workbook to a new file, close that file, and return to my open workbook. Sounds similar to what Dave asked below. I think we're looking for the same solution perhaps. Dave, if I get an answer I'll send it your way. Thanks VBA'ers


Thx....but looking to save 1 sheet from the workbook.

Posted by RJ on June 14, 2001 8:32 AM
Thanks. But I'm trying to save 1 sheet only from the workbook and then allow the user to import that sheet or use data from it later in the original workbook.

As_ FileName:="C:\excel\Book2.xls"

: Seen similar posts, but now answers. What I'm trying to do is place a command button with code that will save 1 sheet from my workbook to a new file, close that file, and return to my open workbook. Sounds similar to what Dave asked below. I think we're looking for the same solution perhaps. Dave, if I get an answer I'll send it your way. Thanks VBA'ers


Re: Thx....but looking to save 1 sheet from the workbook.

Posted by Chuck on June 14, 2001 12:05 PM

Sorry, try this one:

Sheets("NameofSheetToBeCopied").Select
Sheets("NameofSheetToBeCopied").Copy Before:=Workbooks_("NewFile").Sheets(1)
Windows("NameofSheetToBeCopied.xls").Activate

Thanks. But I'm trying to save 1 sheet only from the workbook and then allow the user to import that sheet or use data from it later in the original workbook. : : See if this helps. : Sub Macro2() : ActiveWorkbook.Save : ActiveWorkbook.SaveAs_ FileName:="C:\excel\Book2.xls" : Workbooks.Open FileName:="C:\Excel\Book1.xls" : Workbooks("BOOK2.XLS").Close SaveChanges:=True : End Sub

:


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.