Hi,

I have the following code to take data from one location and record it in
another - this on its own works fine. What I need to do is continuously
record the data so need to move to the next available row so that next time
it copies the information it adds it and as a result creates a list.

Private Sub CommandButton1_Click()
Set wS1 = Worksheets("Menu")
Set wS2 = Worksheets("History of call off")
wS1.Range("C6").Copy wS2.Range("A3")
wS1.Range("C14").Copy wS2.Range("C3")
wS2.Range("B3") = Date
wS1.Activate
End Sub

I am thinking it may be a loop that I need but I really do not have enough
experience with vba to be sure.

Thanks!

Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Hi,

If I understand correctly then try this

Set wS1 = Worksheets("Menu")
Set wS2 = Worksheets("History of call off")
lastrow = wS2.Cells(Cells.Rows.Count, "C").End(xlUp).Row
wS1.Range("C6").Copy wS2.Range("A" & lastrow + 1)
wS1.Range("C14").Copy wS2.Range("C" & lastrow + 1)
wS2.Range("B" & lastrow + 1) = Date

Mike

"Leanne M (Aussie)" wrote:

> Hi,
>
> I have the following code to take data from one location and record it in
> another - this on its own works fine. What I need to do is continuously
> record the data so need to move to the next available row so that next time
> it copies the information it adds it and as a result creates a list.
>
> Private Sub CommandButton1_Click()
> Set wS1 = Worksheets("Menu")
> Set wS2 = Worksheets("History of call off")
> wS1.Range("C6").Copy wS2.Range("A3")
> wS1.Range("C14").Copy wS2.Range("C3")
> wS2.Range("B3") = Date
> wS1.Activate
> End Sub
>
> I am thinking it may be a loop that I need but I really do not have enough
> experience with vba to be sure.
>
> Thanks!
>
> Leanne M (Aussie)
> (Changed Display name from Leanne)
> United Kingdom
> (Don''t ask me what an Aussie is doing living in the UK!)

This will start your copy on row 3 and increment 1 row each time the button
is clicked:

Private Sub CommandButton1_Click()
Dim wS1 As Worksheet, wS2 As Worksheet
Dim nxtRow As Long, lstRow As Long

Set wS1 = Worksheets("Menu")
Set wS2 = Worksheets("History of call off")
nxtRow = 3
lstRow = wS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
If lstRow > nxtRow Then
nxtRow = lstRow
End If
wS1.Range("C6").Copy wS2.Range("A" & nxtRow)
wS1.Range("C14").Copy wS2.Range("C" & nxtRow)
wS2.Range("B" & nxtRow) = Date
wS1.Activate
End Sub


Mike F
"Leanne M (Aussie)" <(E-Mail Removed)> wrote in
message news:9A33E6CC-E941-4F4A-A3FB-(E-Mail Removed)...
> Hi,
>
> I have the following code to take data from one location and record it in
> another - this on its own works fine. What I need to do is continuously
> record the data so need to move to the next available row so that next
> time
> it copies the information it adds it and as a result creates a list.
>
> Private Sub CommandButton1_Click()
> Set wS1 = Worksheets("Menu")
> Set wS2 = Worksheets("History of call off")
> wS1.Range("C6").Copy wS2.Range("A3")
> wS1.Range("C14").Copy wS2.Range("C3")
> wS2.Range("B3") = Date
> wS1.Activate
> End Sub
>
> I am thinking it may be a loop that I need but I really do not have enough
> experience with vba to be sure.
>
> Thanks!
>
> Leanne M (Aussie)
> (Changed Display name from Leanne)
> United Kingdom
> (Don''t ask me what an Aussie is doing living in the UK!)

Thank you both Mikes - they both work fine.

I appreaciate the help

Leanne