VBA Code to Post values to first available rows on a new sheet

[Raustin]

Hello,
I believe this is pretty simple, however i am very new to VB, and i did not see an a clear sample on other searched threads.

I have two sheets, the first one (sheet1) has a set of values in various cells, and (sheet2) is a summary table in which i want sheet1 values to 'post' to the first available row in sheet2.
Example, in sheet one i may have values in A1, B1, and C1. On the click of a button in Sheet1, I would like those 3 values to post in the first available rows of a table in sheet2 (A1:C20)

2nd issue. When all values have been filled up in the table (limit reached) can i have some sort of a pop-up waring? "All available cells have been filled" etc.
Thank you very much for any help,
Regards,
Randy

 

[dave3009]

Perhaps something like

Sub MoveActiveRowAtoC()
Dim lst As Long
Dim CurrRw As Long
lst = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
CurrRw = ActiveCell.Row
If ActiveSheet.Name <> "Sheet1" Then Exit Sub
If lst >= 20 Then 'alter to be the last row of table
    MsgBox "Table Limit Reached!!, data will not be copied"
    Exit Sub
End If
Cells(CurrRw, 1).Resize(1, 3).Copy Sheet2.Cells(lst, 1) 'copy columns A-c of ActiveRow to next available in Sheet2

End Sub

 

[Raustin]

Thank You,
However after creating a button, and entering the code, i get a "Compile error. Expected End Sub" error. (Excel 2007)


Private Sub CommandButton1_Click()
Sub MoveActiveRowAtoC()
Dim lst As Long
Dim CurrRw As Long
lst = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
CurrRw = ActiveCell.Row
If ActiveSheet.Name <> "Sheet1" Then Exit Sub
If lst >= 20 Then 'alter to be the last row of table
MsgBox "Table Limit Reached!!, data will not be copied"
Exit Sub
End If
Cells(CurrRw, 1).Resize(1, 3).Copy Sheet2.Cells(lst, 1) 'copy columns A-c of ActiveRow to next available in Sheet2

End Sub

 

[dave3009]

Remove this line

Sub MoveActiveRowAtoC()

 

[Raustin]

Great, that worked like a charm
Thank you very much
Randy

 

[Raustin]

Hello,
The following code is working ok, however in order to make my sheet complete, I need a bit more help. So for, i have this:

Private Sub CommandButton1_Click()
Dim lst As Long
Dim CurrRw As Long
lst = Sheet5.Range("a" & Rows.Count).End(xlUp).Row + 1
CurrRw = ActiveCell.Row
If ActiveSheet.Name <> "Invoice" Then Exit Sub
If lst >= 20 Then 'alter to be the last row of table
    MsgBox "Table Limit Reached!!, data will not be copied"
    Exit Sub
End If
Cells(CurrRw, 1).Resize(1, 3).Copy Sheet5.Cells(lst, 1) 'copy columns A-c of ActiveRow to next available in Sheet2
End Sub

I actually need 3 things to happen on the click of a button:
1. When the button is clicked on Sheet1, i need the values in specific cells, say A5, B10, and C15 to be copied over to the first available row in a table in sheet2. (As of now the above code only functions when the selected box is in the row to be copied) I would like to have the specific cells copied over, regardless of where the current cell focus is.
2. I would like a set of cells, Say D5:D15 from Sheet1 cleared of their values when the same button is pressed.
2. I would also like a pop-up message letting the user know "Values have been copied" (As of now we have a pop-up when the limit of rows is reached, which is fine as well)

Thanks again for all your help and support, as i am a newbie with VB code.
Randy

 

[dave3009]

Perhaps like,

Private Sub CommandButton1_Click()
Dim lst As Long
Dim i As Variant
Dim j As Long
lst = Sheet5.Range("a" & Rows.Count).End(xlUp).Row + 1
If lst >= 20 Then
    MsgBox "Table Limit Reached!!, data will not be copied"
    Exit Sub
End If
j = 1
For Each i In Array("A5", "B10", "C15")
    Range(i).Copy Sheet5.Cells(lst, j)
    j = j + 1
Next i
    Sheet1.Range("D1:D5").ClearContents
    MsgBox "Values copied successfully!"
End Sub

 

[Raustin]

Wow, great thank you. That works perfectly.
Thanks again for all your help,
Randy