Copy problem again.

[Peter]

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

 

[N10]

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

 

[Peter]

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

 

[ManualMan]

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

 

[Peter]

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