Prev: using Excel 2007 student with Excel 2007 commercial
Next: Keep formatting PC to PC
From: richzip on 22 May 2010 02:17
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!
From: ozgrid.com on 22 May 2010 05:29
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



--
Regards
Dave Hawley
www.ozgrid.com
"richzip" <richzip(a)discussions.microsoft.com> wrote in message
news:D7F8E99C-ABE3-4D8F-BAD7-2DBB42ABDE5F(a)microsoft.com...
>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!

From: Dave Peterson on 22 May 2010 07:06
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.



richzip wrote:
>
> 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!

--

Dave Peterson
 | 
Pages: 1
Prev: using Excel 2007 student with Excel 2007 commercial
Next: Keep formatting PC to PC