0

New to VBA

I am trying to loop through a table and copy the row containing a name. When I run the code, the output is the row below the one I want. 

Sub tblcopypast()

Dim Name As String
Dim tbl As ListObject
Dim i As Integer
Dim lastrow As Integer

Set tbl = ActiveSheet.ListObjects("Table1")
Name = ActiveSheet.Range("E1").Value
lastrow = tbl.ListRows.Count

For i = 1 To lastrow
    If tbl.Range(i, 1) = Name Then
    tbl.ListRows(i).Range.Copy
    ActiveSheet.Range("E5").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    End If
Next

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Frank L.
  • 3
  • 2
  • 3
    `tbl.Range` includes the headers but `tbl.ListRows` does not – Tim Williams Feb 23 '19 at 19:25
  • 1
    You should be able to fix this with `Set tbl = ActiveSheet.ListObjects("Table1").databodyrange` which excludes the header row. –  Feb 23 '19 at 19:31
  • Your answers helped me fix the issues. Thank you – Frank L. Feb 23 '19 at 20:16
  • Note that row counting variables **must** be of type `Long` because Excel has more rows than `Integer` can handle: `Dim lastrow As Long`. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. – Pᴇʜ Feb 25 '19 at 09:11
  • Please don't post the same question twice: https://stackoverflow.com/questions/54846091/vba-copying-from-table-and-pasting-to-create-list – Pᴇʜ Feb 25 '19 at 09:13

0 Answers0