Workbook.add syntax

[Nimrod]

Hello Fello xls'ers
I'm having one of those days where I can't see the error for looking . I'm creating a new workbooks , in a macro, with the following workbook.add command... However when I change the code by inserting .sheets=1 I get an error on the code.

this works

Set newBook = Workbooks.Add
    With newBook
        .Title = CurrentTargBkName
        .Subject = "Branch Mortgage Summary"
        .Comments = "PDR-PVT concept"
        .Author = "Nimrod"
        .SaveAs Filename:=CurrentTargBkName & ".xls"
    End With

this doesnt work

Set newBook = Workbooks.Add
    With newBook
        .Title = CurrentTargBkName
        .Subject = "Branch Mortgage Summary"
        .Comments = "PDR concept"
        .Author = "Nimrod"
        .sheets =1
        .SaveAs Filename:=CurrentTargBkName & ".xls"
    End With

[Ivan F Moala]

On 2002-05-12 15:27, Nimrod wrote:
Hello Fello xls'ers
I'm having one of those days where I can't see the error for looking . I'm creating a new workbooks , in a macro, with the following workbook.add command... However when I change the code by inserting .sheets=1 I get an error on the code.

this works

Set newBook = Workbooks.Add
    With newBook
        .Title = CurrentTargBkName
        .Subject = "Branch Mortgage Summary"
        .Comments = "PDR-PVT concept"
        .Author = "Nimrod"
        .SaveAs Filename:=CurrentTargBkName & ".xls"
    End With

this doesnt work

Set newBook = Workbooks.Add
    With newBook
        .Title = CurrentTargBkName
        .Subject = "Branch Mortgage Summary"
        .Comments = "PDR concept"
        .Author = "Nimrod"
        .sheets =1
        .SaveAs Filename:=CurrentTargBkName & ".xls"
    End With

Hi Nimrod
If you are trying to set the Number of sheets
in the new workbook ? then you have to reset
your current settings for this if it is not 1 eg.

Sub Tester()

Dim newBook As Workbook

Dim OldShcount



OldShcount = Application.SheetsInNewWorkbook

Application.SheetsInNewWorkbook = 1



Set newBook = Workbooks.Add

    With newBook

        .Title = CurrentTargBkName

        .Subject = "Branch Mortgage Summary"

        .Comments = "PDR concept"

        .Author = "Nimrod"

        .SaveAs Filename:=CurrentTargBkName & ".xls"

    End With



'// Reset it back to your orig setting

Application.SheetsInNewWorkbook = OldShcount

Set newBook = Nothing



End Sub

The reason for this is the number of Sheets
in the workbook is a property of the Application and not the Workbook. Have a look
@ the error generated for the clue to this.

[Nimrod]

Thanks Ivan... the problem was really bothering me