View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
DavidH[_2_] DavidH[_2_] is offline
external usenet poster
 
Posts: 16
Default add-in macro strange errors--method sheets of object workbook fail

Help! I'm a macro newbie and this is driving me nuts!

I've tried everything, but I still get erratic behavior from the macro
below. The macro copies a worksheet from a file and inserts as the first
worksheet in the active workbook. I want to distribute the macro to other
Excel users at work, so I'd like to make it as bullet-proof as I can.

If I open Excel and immediately run the macro I get this error on the third
line from the bottom of macro:

Run-time error '-2147221080(800401a8)' method 'sheets' of object '_workbook'
failed.

If I open a previously saved file then run the macro it works fine. It
seemed to run fine when I tested it before saving it as an .xla file.

Any suggestions? There must be some way to avoid this error, or at least
trap it. I've been working on this a few days now with no success, and it's
driving me crazy!

Thanks in advance for the help!

================================
Sub AddDocumentation()

Dim wbDOC As Workbook
Dim CurrentWb As Workbook

Set CurrentWb = ActiveWorkbook


Set wbDOC = Workbooks.Open(Filename:="C:\NRGSCExcelUtil\Docume ntation
Template v7 10 27 04.xls")
wbDOC.Sheets("General Documentation").Copy Befo=CurrentWb.Sheets(1)
ActiveSheet.Name = "General Documentation"
wbDOC.Close SaveChanges:=False
End Sub

==========================================