Help please with copy problem

[Peter]

Hi,

Can anyone please help with a problem I'm having copying data from one
worksheet to another?

On sheet1 I have date in cells AT1 and AT2. What I am trying do do is
copy this data from these two cells into cells in the next free row in
columns A and D in sheet2, i.e. A2 and D2, then next time A3 & D3.

The cde i'm using is:

Sheets("sheet1").Select
Range("AT1").Activate
Selection.Copy
Sheets("sheet2").Activate
Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = Text
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False


Sheets("sheet1").Select
Range("AT2").Activate
Selection.Copy
Sheets("sheet").Activate
Set rng = Cells(Rows.Count, "D").End(xlUp)(2)
rng.Value = Text
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Sheets("sheet1").Select

The problem I'm encountering is that the data from sheet1 AT2 is
always copied into sheet 2 A2 - no sign of the data from AT1 and it
always overwrites, it doesn't copy to the next free row.

Any help/advice/suggestions would be much appreciated.


--
Cheers

Peter

Remove the INVALID to reply

 

[ManualMan]

Hi,

To begin with, your code could be significantly shorter & lots faster

Dim x As Long
'lets start at row 2
x = 2

'pasting Sheet1!AT1 to Sheet2!A2
Worksheets("sheet2").Cells(x,1).Value =
Worksheets("sheet1").Cells(1,46).Value

'pasting Sheet1!AT2 to Sheet2!D2
Worksheets("sheet2").Cells(x,4).Value =
Worksheets("sheet1").Cells(2,46).Value

By incrementing x by 1 you put the next one in the row below the
current one

ManualMan

 

[Peter]

Hi,

To begin with, your code could be significantly shorter & lots faster

Dim x As Long
'lets start at row 2
x = 2

'pasting Sheet1!AT1 to Sheet2!A2
Worksheets("sheet2").Cells(x,1).Value =
Worksheets("sheet1").Cells(1,46).Value

'pasting Sheet1!AT2 to Sheet2!D2
Worksheets("sheet2").Cells(x,4).Value =
Worksheets("sheet1").Cells(2,46).Value

By incrementing x by 1 you put the next one in the row below the
current one

ManualMan

Hi ManualMan,

Thanks very much indeed, it is quick and it does work, but it's not
quite right.

Your script copies from AT1 to A2 and AT2 to D2 - that's just what
I've been struggling with. But the next time I want the data in AT2 &
AT3 to be copied to the next free row in sheet 2, i.e. A3 & D3. I had
hoped that:

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)

would do the job, but it failed miserably - as usual


--
Cheers

Peter

Remove the INVALID to reply