Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

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)


0
bmugabe
Asked:
bmugabe
  • 6
  • 2
1 Solution
 
zonaltechCommented:
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!
0
 
zonaltechCommented:
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.
0
 
bmugabeAuthor Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
zonaltechCommented:
is xlBook a valid object? where did you define it?
0
 
zonaltechCommented:
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.
0
 
zonaltechCommented:
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.
0
 
zonaltechCommented:
*Just a ping, did you figure it out?*
0
 
bmugabeAuthor Commented:
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now