asked on

Copy worksheet into the same workbook

I am writing code in VB6 to populate a workbook with data from a loop. I want the result of each loop to go onto a new worksheet. The first worksheet is a template for all the other worksheets so instead of formatting each worksheet to the same as the template, I want to copy the formatting and formulas on to the next worksheet and then update the values from the loop. When I try to copy the worksheet I get error: Method 'Worksheets' of object '_Global' failed.

The code I am using

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Worksheets(1).Copy After:=xlbook.Worksheets(1)

zonaltech

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q178/5/10.asp&NoWebContent=1

Sometimes it's a pain when 'porting' macros over to VB from Office VBA; every single reference has to be qualified to reference the xlApp object, or else VB tries to access the local Worksheets object in the VB object library. A trick I use to help myself is start any automation code with a:

With xlApp

.Worksheets(1).Copy After:=xlbook.Worksheets(1)

End With

That way you only need to use a "." instead of "xlApp." for every single object. ;) Let me know if this helps!

zonaltech

Clarification: Every single object that isn't ALREADY linked to xlApp (I'm assuming you did a "Set xlBook = xlApp.ActiveWorkBook" or something similar) must be qualified. If you set xlBook in this manner, obviously you don't need to qualify it with xlApp, because it already links to it.

Native VBA has a certain spoil-factor, you get used to all the objects being 'there' already. In VB Automation, you have to tell VB what you're doing with what, every step of the way. I suppose that's it's same as if you tried to automate another application from within Access VBA, you'd have to define and reference all the variables pointing to the other application, or Access would get confused and try to use it's own library for the objects.

bmugabe

ASKER

I tried that i.e. adding

with xlApp
.worksheets(1).copy After:=xlbook.worksheets(1)

End With

Now I get Copy method of worksheet class failed.

zonaltech

is xlBook a valid object? where did you define it?

zonaltech

I'm operating under the assumption that you've omitted code for the sake of shortness. However, if that truly is all the code you have in the procedure, there a quite a few steps to go through before xlBook even points to anything. If that's the case I can help you there too.

zonaltech

Please post the full method if it isn't too large, we can go over opening an instance of Word and assigning it to xlApp object, opening a file from within it, setting that object to xlBook, etc.

ASKER CERTIFIED SOLUTION

zonaltech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bmugabe

ASKER

Yes, I finally got it going. I tried the examples you gave and still got errors either method not supported or method failed. I finally had to split the action into two operations. Adding a new worksheet first then copying the original worksheet onto the added worksheet.

xlbook.Worksheets.Add After:=xlbook.Worksheets(n)

xlsheet.Cells.Copy
xlbook.ActiveSheet.Paste

xlsheet.Activate

I will accept your answer though for the time and helping me to narrow down the problems.