Worksheets.Add method

  • Thread starter Thread starter Bruce Lindsay
  • Start date Start date
B

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.
 
This will work Bruce

Dim oDoc As Workbook
Dim oSht As Worksheet

Set oDoc = Application.ThisWorkbook
oDoc.Worksheets.Add
 
Hi Bruce,

You just had a typo...

oSht = oDoc.Worksheets.Add

should be

Set oSht = oDoc.Worksheets.Add

HTH

Regards,
Kevin
 
Try this variation:

Set oSht = oDoc.Worksheets.Add

Objects need to be assigned to variables with Set.
 
Sorry, ignore me and accept Ron's solution.


Kevin Stecyk said:
Hi Bruce,

You just had a typo...

oSht = oDoc.Worksheets.Add

should be

Set oSht = oDoc.Worksheets.Add

HTH

Regards,
Kevin
Click to expand...
 
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.
 
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))
 
Try

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

Set oDoc = Application.ThisWorkbook
oDoc.Worksheets.Add after:=Sheets(1)
End Sub
 
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))
 
Back
Top