Copy problem again.
 

ManualMan very kindly suggested the script below to enable me to copy
data from cell AT1 of Sheet 1 and paste it to cell A2 of Sheet 2. What
I've been trying to do today, with no success at all, is when when I
do the exercise again, I need to copy from Sheet2 AT1 but now copy to
Sheet1 A3 etc.

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

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

I've tried to get x to increment each time, I've also tried the
following:

Dim x As Long
'lets start at row 2
x = Application.WorksheetFunction.CountA(Range("A:A")) + 1

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

But this insists on writing to row A1, which already has data in it.

I the script to find the next empty cell in column A and write to that
each time i.e. A2 then A3 the A4 etc.

I've also tried, unsuccessfully, to make x equal to a value in another
cell in the worksheet that has the following formula in it:

=COUNT(A2:A10)+2

I had hoped that because this increments each time a row is used, that
it would force the entry to the next free row, but no joy.

Any help/advice/suggestions would be much appreciated.


--
Cheers

Peter

Remove the INVALID to reply

Copy problem again.
 

"Peter" wrote in message
...
ManualMan very kindly suggested the script below to enable me to copy
data from cell AT1 of Sheet 1 and paste it to cell A2 of Sheet 2. What
I've been trying to do today, with no success at all, is when when I
do the exercise again, I need to copy from Sheet2 AT1 but now copy to
Sheet1 A3 etc.

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

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

I've tried to get x to increment each time, I've also tried the
following:

Dim x As Long
'lets start at row 2
x = Application.WorksheetFunction.CountA(Range("A:A")) + 1

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

But this insists on writing to row A1, which already has data in it.

I the script to find the next empty cell in column A and write to that
each time i.e. A2 then A3 the A4 etc.

I've also tried, unsuccessfully, to make x equal to a value in another
cell in the worksheet that has the following formula in it:

=COUNT(A2:A10)+2

I had hoped that because this increments each time a row is used, that
it would force the entry to the next free row, but no joy.

Any help/advice/suggestions would be much appreciated.


--
Cheers

Peter

Remove the INVALID to reply

If the copy and paste references you wish to work with are constant why not
try the macro recorder as a source or workable code ?

Then work to improve the code


Best N10

Copy problem again.
 

On Thu, 4 Nov 2004 23:58:43 +0000 (UTC), "N10"
wrote:


"Peter" wrote in message
.. .
ManualMan very kindly suggested the script below to enable me to copy
data from cell AT1 of Sheet 1 and paste it to cell A2 of Sheet 2. What
I've been trying to do today, with no success at all, is when when I
do the exercise again, I need to copy from Sheet2 AT1 but now copy to
Sheet1 A3 etc.

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

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

I've tried to get x to increment each time, I've also tried the
following:

Dim x As Long
'lets start at row 2
x = Application.WorksheetFunction.CountA(Range("A:A")) + 1

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

But this insists on writing to row A1, which already has data in it.

I the script to find the next empty cell in column A and write to that
each time i.e. A2 then A3 the A4 etc.

I've also tried, unsuccessfully, to make x equal to a value in another
cell in the worksheet that has the following formula in it:

=COUNT(A2:A10)+2

I had hoped that because this increments each time a row is used, that
it would force the entry to the next free row, but no joy.

Any help/advice/suggestions would be much appreciated.


--
Cheers

Peter

Remove the INVALID to reply

If the copy and paste references you wish to work with are constant why not
try the macro recorder as a source or workable code ?

Then work to improve the code


Best N10


Hi,

Thanks for your reply. The copy bit is constant, but the paste bit
isn't - I need to paste in the row below the previous line copied to
each time - this is the bit that I can't get to work.


--
Cheers

Peter

Remove the INVALID to reply

Copy problem again.
 

Hi there,

You could of course try a simple For..Next Loop:

For cnt = 1 To 1000
If Worksheets("Sheet1").Cells(cnt, 1).Value = 0 Then
Worksheets("Sheet1").Cells(cnt, 1).Value =
Worksheets("Sheet2").Cells(1, 46).Value
Exit Sub 'as soon as an empty row is filled,. stop looping
End If
Next cnt

Regards,
Manual Man

Copy problem again.
 

On 6 Nov 2004 02:58:01 -0800, "ManualMan"
wrote:

Hi there,

You could of course try a simple For..Next Loop:

For cnt = 1 To 1000
If Worksheets("Sheet1").Cells(cnt, 1).Value = 0 Then
Worksheets("Sheet1").Cells(cnt, 1).Value =
Worksheets("Sheet2").Cells(1, 46).Value
Exit Sub 'as soon as an empty row is filled,. stop looping
End If
Next cnt

Regards,
Manual Man

Hi Manual Man,

Thanks very much indeed for the suggestion - I shall give it a try.


--
Cheers

Peter

Remove the INVALID to reply