I am trying to copy a worksheet from my add-in to the new workbook when
excel opens. I have activated the add-in, but everytime I open Excel,
I get "Object variable or WIth variable not set".
Here is my code

Private Sub Workbook_Open()
Workbooks("Test.xla").Worksheets(1).Copy
after:=ActiveWorkbook.Worksheets(1)
End Sub

Try this instead:

Private Sub Workbook_Open()
ThisWorkbook.Worksheets(1).Copy _
After:=ActiveWorkbook.Worksheets(1)
End Sub

RBS

"Jay" wrote in message
ups.com...
I am trying to copy a worksheet from my add-in to the new workbook when
excel opens. I have activated the add-in, but everytime I open Excel,
I get "Object variable or WIth variable not set".
Here is my code

Private Sub Workbook_Open()
Workbooks("Test.xla").Worksheets(1).Copy
after:=ActiveWorkbook.Worksheets(1)
End Sub

I am still getting the same error. I've used this before, a long while
ago, I wish I knew what was causing it.
Jason

Hey RBS,
I figured it out. I have to include the workbook.add line of code in
because the add-in actually kicks in before the blank excel book opens,
and if there are no worksheets it returns the error, then opens the
blank excel workbook, without performing the subroutine.
Thanks,
Jay

Yes, that is the one. Remember now making a similar mistake once.

RBS

"Jay" wrote in message
oups.com...
Hey RBS,
I figured it out. I have to include the workbook.add line of code in
because the add-in actually kicks in before the blank excel book opens,
and if there are no worksheets it returns the error, then opens the
blank excel workbook, without performing the subroutine.
Thanks,
Jay