LOOPING multiple ranges

J

Jase

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
 
B

Bernie Deitrick

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).Resize(1,3)
Next i

Otherwise, you will need to code each location individually.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top