Saving a Excel Sheet

[shamigc]

Hi,

I have a workbook with 12 sheets, one for every
month of the year, and I want to make a macro to
save only one sheet with the name of the month,
lets say sheet "01" save as "January.xls". I
developed the following code, but it saved all the
workbook, and I want to save only sheet "01", How can I fix it?
---------------------------------
VBStart
Sub Save_Sheet
Set xlApp = CreateObject("Excel.Application")
Set xlbook =
xlApp.Workbooks.Open("E:\Temp\5421BAvan05.xls")
Set xlsheet = xlbook.Worksheets("01")

xlsheet.Select
xlsheet.Copy
xlsheet.SaveAs "E:\Temp\January.xls"

xlApp.Quit
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlApp = Nothing
End Sub
VBEnd

VBRun>Save_Sheet
----------------------------

Thanks,
Salvador Hernandez

[Bob Hansen]

I think you may have the wrong name for your workbook property.
Try changing:

From:

Set xlsheet = xlbook.Worksheets("01")

To:

Set xlsheet = Workbook.Worksheets("01")
OR
Set xlsheet = Application.Worksheets("01")

---------------------------------------

You may also try replacing Worksheets("01") with Sheets("01"). I believe that there are slight differences, but I don't recall the preferred usage.
----------------------------------------

To see the object names in Excel, do the following to search.
ALT-F11 to open the Visual Basic Editor.
F2 to open Object Browser.
In the Object Browser window, select Excel vs. All Libraries.
On Search results, highlight and click on the Help (yellow questionmark) icon at the upper right of the window. Will see syntax, examples, links to related objects.
-------------------------------------

WorkSheet vs. Sheet

Notes about the WorkSheet property:
Using this property without an object qualifier returns all the worksheets in the active workbook.

This property doesn't return macro sheets; use the Excel4MacroSheets property or the Excel4IntlMacroSheets property to return those sheets.
----------------------------------
Notes about the Sheet property:
Using this property without an object qualifier is equivalent to using ActiveWorkbook.Sheets.
-----------------------------------

[support]

I think Bob has got a little confused actually. Your code is correct. Bob's code might work within Excel but you are correct in using your Object names.

Actually the problem is that the new sheet is copied to a new work book. So rather than saving the current sheet (from which you have copied) you actually want to save the NEW sheet, which is in a NEW workbook. By default new workbooks are called Book1. So modifying your script to the following should work:

VBStart
Sub Save_Sheet
Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.Workbooks.Open("E:\Temp\5421B\Avan05.xls")
Set xlsheet = xlbook.Worksheets("01")

xlsheet.Select
xlsheet.Copy
Set NewBook = xlApp.Workbooks("book1")
NewBook.SaveAs "E:\Temp\January.xls"

xlApp.Quit
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlApp = Nothing
Set NewBook = Nothing
End Sub
VBEnd

VBRun>Save_Sheet