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.

    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


  • 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):

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


    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:

    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!