Copy Multiple Ranges Paste to Designated Ranges

[JHusk]

Copy ranges Accounting!:
AY13:BH13
AL15:BH15

Paste(Special) ranges InvPrint!:
AT6:AY6
D20:AJ20

This is as far as I got regarding the code...

Dim Rng1 As String
Dim Rng2 As String
Dim Rng11 As Sting
Dim Rng22 As String

Rng1 = Range("Accounting!AL15:BH15")
Rng2 = Range("Accounting!AY13:BH13")
Rng11 = Range("InvPrint!D20:AJ20")
Rng22 = Range("InvPrint!AT6:AY6")

I currently have code to make one "selection" at a time and copy/paste. But this requires going back and forth between Accounting! and InvPrint! a minimum of 2 times, but potentially 20+ times. I just see failure of the code at some point with it going back and forth so many times.

 

[DanteAmor]

but potentially 20+ times

Capture the ranges to copy and their corresponding destination cell in the arrays of the following code:

Sub CopyRanges()
  Dim ar1 As Variant, ar2 As Variant
  Dim i As Long
  
  Application.ScreenUpdating = False
  
  ar1 = Array("AY13:BH13", "AL15:BH15")     'Ranges to copy
  ar2 = Array("AT6", "D20")                 'Just initial destination cell
  
  For i = 0 To UBound(ar1)
    Sheets("Accounting").Range(ar1(i)).Copy
    Sheets("InvPrint").Range(ar2(i)).PasteSpecial xlPasteValues
  Next
  
  Application.ScreenUpdating = True
  Application.CutCopyMode = False
End Sub

 

[JHusk]

I was finally able to test it all, but it failed because the source/destination ranges are different sizes. any suggestions?

I added this, but still didn't work...

Sheets("Accounting").Range(ar1(i)).Copy
    Sheets("InvPrint").Range(ar2(i)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[JHusk]

Capture the ranges to copy and their corresponding destination cell in the arrays of the following code:

I can think of two other options if that's not possible. One I know how to do, one I think I can figure out.

#1 Create identical sized ranges on the destination sheet, paste to them, and then reference into the cells I need to the text.
#2 Reference the source ranges on the destination sheet, then apply VBA to new sheet to stop automatic calculations. I could setup the destination sheet with this coding already built into it, but do the reference work to start with?

 

[DanteAmor]

I was finally able to test it all, but it failed because the source/destination ranges are different sizes. any suggestions?

The source is a range, the destination is just the starting cell.
Which problem you have?

 

[JHusk]

The source is a range, the destination is just the starting cell.
Which problem you have?

It's saying that the size of the merged cells have to be the same size. I'm guessing that's an issue, first time I've come up across this.

Source AY13:BH13 is merged, destination AT6:AY6 is merged.

 

[DanteAmor]

It's saying that the size of the merged cells have to be the same size. I'm guessing that's an issue, first time I've come up across this.

Source AY13:BH13 is merged, destination AT6:AY6 is merged.

You should not use merged cells.
In order for the macro to fix that problem, you will need to modify the macro according to the merged cells, do some testing and let the macro run.
My recommendation and that of anyone here in the forum is that you do not use merged cells, you will save yourself a lot of headaches.

 

[JHusk]

You should not use merged cells.
In order for the macro to fix that problem, you will need to modify the macro according to the merged cells, do some testing and let the macro run.
My recommendation and that of anyone here in the forum is that you do not use merged cells, you will save yourself a lot of headaches.

I understand and appreciate your help. I wasn't able to use your code in the way I had intended but I was able to use it for what I needed to accomplish, just had to tweak it a bit to accommodate the merged cells. Basically, I used the range copy from the source sheet, then pasted to a single cell on the destination sheet. From there I just had to tell the cells in the print area of that sheet where to look for information. And then hid all of the rows/columns with the pasted data.

Works perfectly, I greatly appreciate your help...