Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing relative cell reference
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing relative cell reference
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing relative cell reference
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative cell reference | Excel Discussion (Misc queries) | |||
Changing Cells from Relative to Absolute Reference | Excel Discussion (Misc queries) | |||
Relative Cell Reference | Excel Worksheet Functions | |||
changing from relative to absolute cell reference in multiple form | Excel Worksheet Functions | |||
changing multiple cells from relative to absolute reference | Excel Discussion (Misc queries) |