Code for next row

[Leanne M (Aussie)]

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!)

 

[Mike H]

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

 

[Mike Fogleman]

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)]

Thank you both Mikes - they both work fine.

I appreaciate the help

Leanne