Copying cells from one sheet to another

  • Thread starter Thread starter Andrew
  • Start date Start date
A

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.
 
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.
 
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.
 
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.
 
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
....
 
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
 
Back
Top