Worksheets.Add method

[Bruce Lindsay]

I have tried to add a worksheet with the following code.

dim oDoc as Workbook
dim oSht as Worksheet

Set oDoc = Application.ThisWorkbook
oSht = oDoc.Worksheets.Add

It gives an error #1004. I have tried it in many variations but all fail.
Some even create the sheet but still error out. Any help would be
appreciated.

 

[Ron de Bruin]

This will work Bruce

Dim oDoc As Workbook
Dim oSht As Worksheet

Set oDoc = Application.ThisWorkbook
oDoc.Worksheets.Add

 

[Kevin Stecyk]

Hi Bruce,

You just had a typo...

oSht = oDoc.Worksheets.Add

should be

Set oSht = oDoc.Worksheets.Add

HTH

Regards,
Kevin

 

[J.E. McGimpsey]

Try this variation:

Set oSht = oDoc.Worksheets.Add

Objects need to be assigned to variables with Set.

 

[Kevin Stecyk]

Sorry, ignore me and accept Ron's solution.

Hi Bruce,

You just had a typo...

oSht = oDoc.Worksheets.Add

should be

Set oSht = oDoc.Worksheets.Add

HTH

Regards,
Kevin

 

[J.E. McGimpsey]

Your solution was fine.

 

[Bruce Lindsay]

Thanks guys for your help. When I placed it in the code with nothing else it
works. However, if I add "after:=1 then it errors out. Thanks for the help.

 

[Kevin Stecyk]

J.E. McGimpsey,

Thank you.

Regards,
Kevin

Your solution was fine.

 

[J.E. McGimpsey]

Please take a look at VBA Help for the Worksheets.Add method.

The After argument must be an object (i.e. a Sheet), not a value.

Try:

Set oSht = oDoc.Worksheets.Add(After:=Sheets(1))

 

[Ron de Bruin]

Try

Sub test()
Dim oDoc As Workbook
Dim oSht As Worksheet

Set oDoc = Application.ThisWorkbook
oDoc.Worksheets.Add after:=Sheets(1)
End Sub

 

[Tom Ogilvy]

Remember this two days ago?

Dim oDoc As Workbook
Dim oSht As Worksheet
Set oDoc = Application.ThisWorkbook
Set oSht = oDoc.Worksheets.Add(after:=oDoc.Worksheets(1))