Copying cells from one sheet to another

[Andrew]

I have data in sheet one, A1 thru A50, and in another sheet these cells are
referneced. I need to copy as follows:

Sheet 1 A1 to Sheet 2 A1
Sheet 1 A2 to Sheet 2 A10
Sheet 1 A3 to Sheet 2 A20
Sheet 1 A4 to Sheet 2 A30
and so on....

but everytime I try to copy Sheet 2 A10 gets the data from Sheet1 A10 and
not A2. How do I get around this?

Thanks.

P.S. I am in office 2000 and 2003.

 

[Luke M]

Try this:

=INDIRECT("'Sheet 1'!A"&INT(ROW()/10)+1)

Placing this in row 1 references A1, rows 10 - 19 references A2, rows 20-29
references A3, etc.

 

[NoodNutt]

G'day Andrew

Something like this should do the trick for you..

Sub Copy_MyCells()

Application.ScreenUpdating = False
Sheets("1").Select
Range("A:1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("2").Select
Range("A:1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Sheets("1").Select
Range("A:2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("2").Select
Range("A:10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Etc.......
Etc.......
Etc.......

Application.ScreenUpdating = True

End Sub

HTH

Regards
Mark.

 

[David Biddulph]

In Sheet2!A1 use the formula
=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),10)=0,INDIRECT("Sheet1!A"&(ROW()/10)+1),""))or=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),10)=0,OFFSET(Sheet1!A$1,ROW()/10,0),"")) and copy downIt's interesting that you have 9 rows difference between the first twodestinations, but 10 rows difference between subsequent pairs.--David Biddulph"Andrew" <[email protected]> wrote in messagehave data in sheet one, A1 thru A50, and in another sheet these cells are> referneced. I need to copy as follows:>> Sheet 1 A1 to Sheet 2 A1> Sheet 1 A2 to Sheet 2 A10> Sheet 1 A3 to Sheet 2 A20> Sheet 1 A4 to Sheet 2 A30> and so on....>> but everytime I try to copy Sheet 2 A10 gets the data from Sheet1 A10 and> not A2. How do I get around this?>> Thanks.>> P.S. I am in office 2000 and 2003.

 

[Andrew]

First, thanks for everyone's help.

David, your solution seems to be working the best. How would I adjust it
work on odd rows like 3, 13, 23.... I have another place I would like to use
it as well. I have been play around with it but I keep getting errors. This
would be

S1 A3 to S2 A3
S1 A4 to S2 A13
S1 A5 to S2 A23
....

 

[Jacob Skaria]

Hi Andrew

Try the below formula in Sheet2 A3 and copy down as required...
=IF(MOD(ROW(),10)=3,INDIRECT("Sheet1!A"&(INT(ROW()/10))+3),"")

for 4,14,24 from Sheet1 4,5,6 try...
=IF(MOD(ROW(),10)=4,INDIRECT("Sheet1!A"&(INT(ROW()/10))+4),"")

If this post helps click Yes