Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for next row
Thank you both Mikes - they both work fine.
I appreaciate the help Leanne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |