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

Raustin

Board Regular
Joined
Mar 21, 2011
Messages
50
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Perhaps something like

Code:
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

Board Regular
Joined
Mar 21, 2011
Messages
50
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Remove this line

Code:
Sub MoveActiveRowAtoC()
 

Raustin

Board Regular
Joined
Mar 21, 2011
Messages
50

ADVERTISEMENT

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

Raustin

Board Regular
Joined
Mar 21, 2011
Messages
50
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:
Code:
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Perhaps like,

Code:
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

Board Regular
Joined
Mar 21, 2011
Messages
50
Wow, great thank you. That works perfectly.
Thanks again for all your help,
Randy
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top