LOOPING multiple ranges
 

I have a looping code that grabs a range from 1 page and pastes onto another.
My only problem is that I need to grab multiple ranges for example
range("H31:J31") and range("J33:L33") and paste them into Sheet1 Range("D32")
and Range("E43") respectively. Any idea on how to do multiple ranges?

Dim DestCell As Range
' Dim RngToCopy As Range
'
' With Worksheets("Sheet2")
' Set RngToCopy = .Range("H31:J31")
' End With
'
' With Worksheets("Sheet1")
' Set DestCell = .Range("D32")
' End With
'
' Do
' If Application.CountA(RngToCopy) = 0 Then
' Exit Sub
'
' Else
' RngToCopy.Copy
' DestCell.PasteSpecial Paste:=xlPasteValues
'
' Set RngToCopy = RngToCopy.Offset(50, 0)
' Set DestCell = DestCell.Offset(50, 0)
'
' End If
' Loop

LOOPING multiple ranges
 

Jase,

IF there is an logic to the spacing of the ranges and their respective targets, you can set up a
loop. For Example:

For i = 0 to 2
Worksheets("Sheet2").Range("H31").Offset(i*2,i*2). Resize(1,3).Copy _
Worksheets("Sheet1").Range("D32").Offset(i*2,0).Re size(1,3)
Next i

Otherwise, you will need to code each location individually.

HTH,
Bernie
MS Excel MVP


"Jase" wrote in message
...
I have a looping code that grabs a range from 1 page and pastes onto another.
My only problem is that I need to grab multiple ranges for example
range("H31:J31") and range("J33:L33") and paste them into Sheet1 Range("D32")
and Range("E43") respectively. Any idea on how to do multiple ranges?

Dim DestCell As Range
' Dim RngToCopy As Range
'
' With Worksheets("Sheet2")
' Set RngToCopy = .Range("H31:J31")
' End With
'
' With Worksheets("Sheet1")
' Set DestCell = .Range("D32")
' End With
'
' Do
' If Application.CountA(RngToCopy) = 0 Then
' Exit Sub
'
' Else
' RngToCopy.Copy
' DestCell.PasteSpecial Paste:=xlPasteValues
'
' Set RngToCopy = RngToCopy.Offset(50, 0)
' Set DestCell = DestCell.Offset(50, 0)
'
' End If
' Loop