Copy ranges one workbook to another

  • I am having trouble with what I thought was a simple bit of code to copy a range from one workbook to another. Both are assumed open at the time, but will change in name frequently so are set up (or attempt to set up) as names defined at top. The full macro will work on multiple ranges and sheets, but I have just posted core/repetitive code here.


    I get Compile error Type mismatch at first hurdle (Set wb1 = ...); not clear what errors I may get on remainder.


    Code
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set wb1 = ("2017.07.09 xxx yyy zzz v18.xlsm")
    Set wb2 = ("2017.07.09 results output.xlsm")
    Set ws1 = Worksheets("Output Results Sheet")
    Set ws2 = Worksheets("CityName")
    wb1.ws1.Range("O8:P13").Copy wb2.ws2.Range("N5:O10")
  • Re: Copy ranges one workbook to another


    Try

  • Re: Copy ranges one workbook to another


    Thank you. That worked; regrettably no cigar yet


    I had failed to specify that I wanted to paste value. So I modified (or tried to):


    Code
    ws1.Range("O8:P13").Copy ws2.Range("N5:O10:)


    to

    Code
    ws1.Range("O8:P13").Copy ws2.Range("N5:O10:).PasteSpecial Paste:=xlPasteValues


    To that I get a compile error "unexpected end of statement"


    I also tried breaking out the one line code into three:

    Code
    ws1.Range("O8:P13").Copy 
    ws2.Range("N5:O10:).Select
    Selection.PasteSpecial Paste:=xlPasteValues


    This works but is a lot longer. Is there a short way using the one line of code originally there ?

  • Re: Copy ranges one workbook to another


    I speak too soon. It works ... but only under certain circumstances.


    If I run the macro from the sheet I am pasting to (ws2), it works


    If I try and run it from any other sheet in the macro, or if I try and run it from ws1 (the other workbook), I get a run time error "method of Range class failed", and the debugger highlights the Range.Select code (the second of the three lines above)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!