Changing relative cell reference

[richzip]

I want to copy data from one work sheet (A) to another (B); instead of using
a normal relative cell reference (i.e., copy A1 to A1, A2 to A2, etc), I want
each row in worksheet A to be copied to every 31st row in worksheet A:

A2 in worksheet A should be copied to A2 in worksheet B
A3 in worksheet A should be copied to A32 in worksheet B
A4 in worksheet A should be copied to A63 in worksheet B .. etc

Thanks for your help!

 

[ozgrid.com]

Try something like;


Sub DoIt()
Dim wba As Workbook, wbb As Workbook
Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim rCell As Range, lStop As Long
Dim lrow As Long

Set wba = ThisWorkbook
Set wsCopy = wba.Sheets(1)
Set wbb = Workbooks("Book1.xls")
Set wsPaste = wbb.Sheets(1)
lrow = 2

With wsCopy
lStop = .Cells( _
.Rows.Count, 1).End(xlUp).Row
End With

For Each rCell In Range("A1:A" & lStop)
rCell.Copy wsPaste.Cells(lrow, 1)
lrow = lrow + 31
Next rCell




End Sub

 

[Dave Peterson]

I'm confused by what you want.

A2, A32, A63, ...
Doesn't have the pattern you describe.

If you meant:
A2,A33,A64,A95, ...
You could put this formula in A2:
=INDEX('worksheet a'!A:A,(ROW()-2)*31+2)

if you meant:
A2,A32,A62,A92, ...
You could put this formula in A2:
=INDEX('worksheet a'!A:A,(ROW()-2)*30+2)

In either case, drag the formula down as far as you need.