Hi,

I am trying to get a script to work that will copy data from one sheet
of a workbook to the next free row of a different sheet in the same
workbook. What i want to do is increment the value of "x" in the first
line below each time the script is used:

Dim x As Long

x = Application.WorksheetFunction.CountA(Range("A:A")) + 2

Worksheets("Sheet 1").Cells(x, 2).Value = Worksheets("Sheet
2").Cells(1, 46).Value

Worksheets("Sheet 1").Cells(x, 3).Value = Worksheets("Sheet
2").Cells(2, 46).Value

i.e

First time script is run:

x = Application.WorksheetFunction.CountA(Range("A:A")) + 2

Next time script is run:

x = Application.WorksheetFunction.CountA(Range("A:A")) + 3

etc.

Any help/advice/suggestions would be much appreciated.
--
Cheers

Peter

Remove the INVALID to reply

Peter,

Try this

Static x As Long

If x = 0 Then x = 2
With Worksheets("Sheet 1")
x = Application.WorksheetFunction.CountA(Range("A:A"))

.Cells(x, 2).Value = Worksheets("Sheet2").Cells(1, 46).Value
.Cells(x, 3).Value = Worksheets("Sheet2").Cells(2, 46).Value
End With
x = x + 1


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter" wrote in message
...
Hi,

I am trying to get a script to work that will copy data from one sheet
of a workbook to the next free row of a different sheet in the same
workbook. What i want to do is increment the value of "x" in the first
line below each time the script is used:

Dim x As Long

x = Application.WorksheetFunction.CountA(Range("A:A")) + 2

Worksheets("Sheet 1").Cells(x, 2).Value = Worksheets("Sheet
2").Cells(1, 46).Value

Worksheets("Sheet 1").Cells(x, 3).Value = Worksheets("Sheet
2").Cells(2, 46).Value

i.e

First time script is run:

x = Application.WorksheetFunction.CountA(Range("A:A")) + 2

Next time script is run:

x = Application.WorksheetFunction.CountA(Range("A:A")) + 3

etc.

Any help/advice/suggestions would be much appreciated.
--
Cheers

Peter

Remove the INVALID to reply