Code for next row
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!) |
Code for next row
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!) |
Code for next row
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)" wrote in message ... 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!) |
Code for next row
Thank you both Mikes - they both work fine.
I appreaciate the help Leanne |
All times are GMT +1. The time now is 03:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com