Passing values of range with multiple areas from one sheet to another

[paparingos7]

Hi,

I've got two identical ranges on two different sheets (basically the second sheet gets created by copying the first).

The ranges both have multiple areas, i.e. they look something like:

$F$21:$I$23, $F$13:$F$16, $C$26:$C$28, $C$33, $F$26:$I$28, $C$37:$C$39, $C$44, $F$37:$I$39

Like I said the two ranges are identical.

I want to 'pass' the values from the second range to the first, i.e. transfer

$F$21:$I$23 from the 2nd sheet into the 1st sheet

and then

$F$13:$F$16 from the 2nd sheet into the 1st sheet

and so on.


Some guidance please about how to structure the code?

Many thanks

 

[JoeMo]

Hi,

I've got two identical ranges on two different sheets (basically the second sheet gets created by copying the first).

The ranges both have multiple areas, i.e. they look something like:

$F$21:$I$23, $F$13:$F$16, $C$26:$C$28, $C$33, $F$26:$I$28, $C$37:$C$39, $C$44, $F$37:$I$39

Like I said the two ranges are identical.

I want to 'pass' the values from the second range to the first, i.e. transfer

$F$21:$I$23 from the 2nd sheet into the 1st sheet

and then

$F$13:$F$16 from the 2nd sheet into the 1st sheet

and so on.


Some guidance please about how to structure the code?

Many thanks

Assuming the two sheets are named Sheet1 and Sheet2 and that you want to transfer the data from Sheet1 to Sheet2 try this (adjust source and receiver sheet names to suit):

Sub TransferMultipleAreasData()
Dim myRngs As Variant, S() As Range, R() As Range, i As Long
'Assume sheet1 is the source sheet, sheet2 is the receiver sheet
myRngs = Array("$F$21:$I$23", "$F$13:$F$16", "$C$26:$C$28", "$C$33", "$F$26:$I$28", "$C$37:$C$39", "$C$44", "$F$37:$I$39")
ReDim S(LBound(myRngs) To UBound(myRngs))
With Sheets("Sheet1")
    For i = LBound(myRngs) To UBound(myRngs)
        Set S(i) = .Range(myRngs(i))
    Next i
End With
ReDim R(LBound(myRngs) To UBound(myRngs))
Application.ScreenUpdating = False
With Sheets("Sheet2")
    For i = LBound(myRngs) To UBound(myRngs)
        Set R(i) = .Range(myRngs(i))
        R(i).Value = S(i).Value
    Next i
End With
End Sub