Copy Worksheet from one file to another

Question

Hi,

I have seen solutions for this in many sites but I can seem to get it working.  I have been succesful at opening 2 workbooks and selecting what I want to copy from one workbook but I cannot successfully copy it into another file. 

My source workbook (workbook1) is: 

Set xlWB = xlApp.Workbooks.Open("c:\temp\test.xls")
Set xlWs = xlWB.ActiveSheet
 
my target workbook (workbook2) is:

Set xlWB2 = xlApp.Workbooks.Open("c:\temp\test2.xls")

The range I want to copy from Workbook1 is:   Rows(3:49).Select

Could someone help me figure out how to copy this to Workbook2?

Thanks!!!!!

Answer 1

Hi,

Believe it or not I think a copy and paste would do the trick.

    Range("B3:I24").Select
    Selection.Copy
    Windows("Book2").Activate
    Range("C3").Select
    ActiveSheet.Paste

You'll need to prefix the code above with the xlWs and xlWB2 variables that you have but it shouldn't be any bother.

---

www.dsmyth.net | www.dsmyth.net/wiki

Answer 2

Hey Derek,




Condor,

just keep in mind that the second workbook will be just another object to work with.  for example in workbook 1 say you have only 2 cells.  those are 2 different objects in the same workbook.  so you now have 2 workbooks in the same xlapp.  you can refer to them respectively.  by the names you have given them (xlwb and xlwb2).  and inside of these objects you can access their objects, such as worksheets, cells, etc... and all the functions that you can do with them. 

hope that makes some sense about it.  and i know why derek said believe it or not about the paste.  it has been a while since i really played with all the vba code also.  i think you can be a little more direct about all this as well without having to use the select methods and just use the copy method.  i believe the range has a copy method.  i just haven't played with it in a while.  so in other words rather than select the range, copy, then select the other range, copy.  you should be able to just specify the 2 ranges and copy.  some of the vba people can probably tell you the exact method to use. 

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial