VB Code to post values from one sheet to another

[Raustin]

Hello i could you some help with my VB code.
I am having a bit of trouble figuring out how to post values from specific cells in one sheet, say (A5, C10, D15, and E20) to a table on a second sheet on the click of a button. Values in the 2nd sheet will post to the first available row in a table until 20 rows are reached. The code i have is not working because it only posts cells from active row. This is the code i currently have:

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

Your help here would be greatly appreciated,
Randy

 

[texasaswang]

Randy,
Does the value location vary? Is there always a value there? Could you post a sample of the data you are working with?

-Joe

 

[Raustin]

Hi Joe,
thank you for the question. What i have is an invoice on the first sheet, along with customer name, invoice # and invoice total. On the 2nd sheet i have a summary page. I basically want to post the invoice details from the first page, onto a single row in the table on the 2nd summary sheet. The columns in this summary sheet will be Customer Name, Invoice # and Invoice Total. While the values on the invoice page will change, their locations will remain the same. And on the summary sheet, all of these values will be posting to the first available row in this table.
Thank you I hope this helps,
Randy

 

[texasaswang]

It appears you need to add additonal code for your other values. Assuming your values are in the same row on the invoice page....

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
Cells(CurrRw, 5).Resize(1, 3).Copy Sheet5.Cells(lst, 2)
Cells(CurrRw, 10).Resize(1, 3).Copy Sheet5.Cells(lst, 3)
End Sub

If the 2nd/3rd data cells are in different rows, add a line between each saying:
CurrRw = CurrRw + 5 (Or whatever distance it is to the next value)

It is not elegant, but it should work.

 

[Raustin]

Hi Joe,
Thanks for the code. I have implemented it, and some parts are working well, however, it should not matter what cell is currently selected, I would still like specific cells copied over, regardless of 'Current Row' (or what cell is currently selected should not have a bearing on what cells are copied over)

This is the idea i had but I would need the proper:

.Copy Sheet5.Cells(lst, 1) = Sheet1 (Range"a5")
.Copy Sheet5.Cells(lst, 2) - Sheet 1 (Range "b10")
.Copy Sheet5.Cells(lst, 3) = Sheet1 (Range "c15")

The idea is to take the specific cells in Sheet1 (Invoice) and copy them to the first available row in the summary sheet.

I am just not sure what the proper code for that would be.
Thanks again so much for all your help,
Randy

 

[Raustin]

Hi Joe,
I think i got it. This seems to work so far.

Sheet5.Cells(lst, 1) = Range("g10")
Sheet5.Cells(lst, 2) = Range("j4")
Sheet5.Cells(lst, 3) = Range("j41")
Sheet5.Cells(lst, 4) = Range("n38")
Sheet5.Cells(lst, 5) = Range("n41")
Sheet5.Cells(lst, 6) = Range("j5")

Thanks again for all your help,
Randy