How to create a worksheet in new workbook

[sridharkk]

How do we create a worksheet in new workbook

Dim wb As Workbook
Set wb = Workbooks.Add(xlWBATWorksheet)
wb.Worksheets(1).Name = "Pay-Per-Click"
wb.Sheets.Add (xlWorksheet)
wb.Worksheets(2).Name = "Categories"

This returns an error back Application Defined - Object Defined error

Thanks,
Sridhar

 

[Cbrine]

dim wb as workbook, ws as worksheet
set wb = workbooks.add(xlWBATWorksheet)
set ws = wb.sheets.add
ws.name ="Categories

This should do what you want.

HTH
Cal

 

[TheNoocH]

try this

Sub test()
Dim wb As Workbook
Set wb = Workbooks.Add(xlWBATWorksheet)
wb.Worksheets(1).Name = "Pay-Per-Click"
wb.Sheets.Add After:=Worksheets(Worksheets.Count)
wb.Worksheets(2).Name = "Categories"
End Sub

 

[Joe Was]

Sub myWBAdd()
'Standard Module code, like: Module1
Dim myShCount%

Workbooks.Add
For Each Sh In Worksheets
myShCount = myShCount + 1
Next Sh

Sheets.Add
ActiveSheet.Name = "Tests"

Sheets("Tests").Move After:=Sheets(myShCount + 1)
Range("A1").Select
End Sub

Sub myWBAddOnly()
'Standard Module code, like: Module1

Workbooks.Add
Sheets.Add
ActiveSheet.Name = "Tests"

For Each Sh In Worksheets
If Sh.Name <> "Tests" Then Sh.Delete
Next Sh
End Sub