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.

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.

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andrew" wrote:

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.

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" wrote in ...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.