Worksheets.Add method
 

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.

Worksheets.Add method
 

This will work Bruce

Dim oDoc As Workbook
Dim oSht As Worksheet

Set oDoc = Application.ThisWorkbook
oDoc.Worksheets.Add


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Bruce Lindsay" wrote in message ...
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.

Worksheets.Add method
 

Hi Bruce,

You just had a typo...

oSht = oDoc.Worksheets.Add

should be

Set oSht = oDoc.Worksheets.Add

HTH

Regards,
Kevin


"Bruce Lindsay" wrote in message
...
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.

Worksheets.Add method
 

Try this variation:

Set oSht = oDoc.Worksheets.Add

Objects need to be assigned to variables with Set.

In article ,
"Bruce Lindsay" wrote:

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.

Worksheets.Add method
 

Sorry, ignore me and accept Ron's solution.


"Kevin Stecyk" wrote in message
...
Hi Bruce,

You just had a typo...

oSht = oDoc.Worksheets.Add

should be

Set oSht = oDoc.Worksheets.Add

HTH

Regards,
Kevin


"Bruce Lindsay" wrote in message
...
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.

Worksheets.Add method
 

Your solution was fine.

In article ,
"Kevin Stecyk" wrote:

Sorry, ignore me and accept Ron's solution.

Worksheets.Add method
 

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.

"Bruce Lindsay" wrote in message
...
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.

Worksheets.Add method
 

J.E. McGimpsey,

Thank you.

Regards,
Kevin


"J.E. McGimpsey" wrote in message
...
Your solution was fine.

In article ,
"Kevin Stecyk" wrote:

Sorry, ignore me and accept Ron's solution.

Worksheets.Add method
 

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))

In article ,
"Bruce Lindsay" wrote:

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.

"Bruce Lindsay" wrote in message
...
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.

Worksheets.Add method
 

Try

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

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Bruce Lindsay" wrote in message ...
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.

"Bruce Lindsay" wrote in message
...
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.

Worksheets.Add method
 

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))



--
Regards,
Tom Ogilvy


Bruce Lindsay wrote in message
...
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.