VB Code not working right (Post values to first available row)

[Raustin]

Hello,
I have this code that is not working exactly right. I am attempting to get some values of one sheet, and post them to a table on a second sheet. On the first sheet they are copied from specific cells each time. They are being copied to a table on the 2nd sheet in which i need the values copied to the first available row. As of now they are being copied after the last line of values (even if there are rows in between which have no values). Basically. i have a last "Totals" row at the bottom of this table, and i must have the values posted to rows above this line, not below. Here is my current code:

Private Sub CommandButton1_Click()
Dim lst As Long
Dim CurrRw As Long
lst = Sheet5.Range("a" & Rows.Count).End(xlUp).Row + 1
If ActiveSheet.Name <> "Invoice" Then Exit Sub
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")
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
End Sub

Thank you for your help,
Randy

 

[MickG]

Perhaps something like this:-

Dim lst As Long
lst = sheets("Sheet5").Range("A1").End(xlDown).row + 1
MsgBox lst

 

[Raustin]

Hi,
When i replaced the line, i got a 'Subscript out of range' error.
Here is the current code:

Private Sub CommandButton1_Click()
MsgBox "Invoice Posted"
Dim lst As Long
[COLOR="Red"]lst = Sheets("Sheet5").Range("A2").End(xlDown).Row + 1[/COLOR]
If ActiveSheet.Name <> "Invoice" Then Exit Sub
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")
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
End Sub

 

[texasaswang]

So, you need to insert a line before doing your copy.

Something like:

Range("A1").Select
Selection.End(xldown).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

This should take you to the total line, and insert a blank line.

 

[Raustin]

Thank you very much for all your help. That did the trick.
Much appreciated,
Randy