VBA: Copy Paste Multiple Ranges Into Another Workbook

[losamfr17]

Hello,

I want to copy the ranges from Workbook_A Sheet(1) into the exact same cells of Workbook_B Sheet(1).
Why won't this work and what is the best way to do that please?

Windows("Workbook_A").Sheets(1).Range("P4:R7,N22:N47, AC4:AE7,AA22:AA47, AP4:AR7,AN22:AN47, BC4:BE7,BA22:BA47, BP4:BR7,BN22:BN47").Copy

Thank you.

 

[mumps]

Try:

WorkBooks("Workbook_A.xlsx").Sheets(1)

 

[losamfr17]

The issue is really that Excel won't allow to copy multiple ranges at once.

 

[mumps]

This should work:

With Workbooks("Workbook_A.xlsx").Sheets(1)
    .Range("P4:R7, AC4:AE7, AP4:AR7, BC4:BE7, BP4:BR7").Copy
    .Range("N22:N47,AA22:AA47,AN22:AN47,BA22:BA47,BN22:BN47").Copy
End With

 

[losamfr17]

I get an error message saying "that command cannot be used on multiple selections."
I am using Excel 2013. Is it working on your end?

 

[mumps]

I tried it on some dummy data and it worked for me. Can you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Fluff]

How about

Dim Rng As Range
For Each Rng In Workbooks("Workbook_A.xlsm").Sheets(1).Range("P4:R7,N22:N47, AC4:AE7,AA22:AA47, AP4:AR7,AN22:AN47, BC4:BE7,BA22:BA47, BP4:BR7,BN22:BN47")
   Rng.Copy Workbooks("Workbook_B.xlsm").Sheets(1).Range(Rng.Address)
Next Rng

 

[losamfr17]

Fluff to the rescue!!!
Thank you Sir!
I learn new thing every time I post here.

 

[Fluff]

Glad we could help & thanks for the feedback