add-in problems

[Jay]

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

 

[RB Smissaert]

Try this instead:

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

RBS

 

[Jay]

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

 

[Jay]

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

 

[RB Smissaert]

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

RBS