Copying Entire Worksheets into a new workbook with VBA- Help

[sll810]

I am writing VBA in access controlling Excel. I need to open a new workbook and copy the first sheet, called Summary into a new sheet in the new workbook and then save the new workbook. I keep getting an error saying "Copy method of Worksheet class failed"

Here is my copy code below:

Set xlapp = New Excel.Application
xlapp.Visible = True
Set reportbook = xlapp.Workbooks.Add
scrapbook.Worksheets("Summary").Copy_
Before:=reportbook.Worksheets(1)

I have tried tio use the name of a worksheet also when referencing the reportbook.

Any ideas are MUCH appreciated.

Thanks!!

 

[xenou]

I may be wrong but it sounds like you are dealing with two workbooks (the new one you added and another you are copying a sheet from).

Be sure you fully reference the workbooks that you copy from and copy to:

This sub copies a sheet called "Sheet1" from Book1.xls and places it before the first sheet in Book2.xls.

Sub test()
    
    Workbooks("Book1.xls").Activate
    Workbooks("Book1.xls").Sheets("Sheet1").Copy _
        Before:=Workbooks("Book2.xls").Worksheets(1)
    
    End Sub

A further issue may arise if you are creating a new instance of Excel - does it recognize workbooks open in another instance? Not sure here...

HTH