VBA - Results Populating Outside Excel Table

[MishTaylor]

Hi Everyone,

I need some help with my code to reference the table properly. Right now, when I run this code, the data populates outside of the table - not sure how to reference the table correctly.

Table = "PrevReport"
Sheet = "Print Reports"
The data is being filtered by date range from another sheet called "Packaged&Restock"

Thanks!

Private Sub PreviewBtn_Click()
Dim i As Long
Dim startdate As Date
Dim enddate As Date
Dim ws As Worksheet
Set RTAB = ws.ListObjects("PrevReport")
Set REP = ThisWorkbook.Sheets("PrintReports")
Set TOT = ThisWorkbook.Sheets("Packaged&Restock")
Application.ScreenUpdating = False

startdate = REP.Range("H2").Value
enddate = REP.Range("I2").Value


RTAB.Range("A" & 11, "I" & 100000).ClearContents

For i = 2 To TOT.Range("A100000").End(xlUp).Row

    If TOT.Cells(i, 1) >= startdate And _
    TOT.Cells(i, 1) <= enddate Then

RTAB.Range("A100000").End(xlUp).Offset(1, 0) = TOT.Cells(i, 1)
For x = 1 To 10
RTAB.Range("A100000").End(xlUp).Offset(0, x) = TOT.Cells(i, x + 1)
Next x


End If
Next i

End Sub

 

[MishTaylor]

As a workaround, you could add this line as your last line of code...

  RTAB.ListRows(1).Delete

Also, I don't have your data but this code is not picking up row 2 of my mock-up of your "Packaged&Restock" sheet. I don't know if that is what you intended...

I am unsure why it's doing that but I've loaded new data (sending a screenshot of it) and I'm getting a lot of blank rows in between all the data now and for some reason one of the columns is being skipped. I have to tweak it. The only difference is this time the data in Packaged&Restock has multiple dates and I'm filtering for 1 day. I think it's copying the whole table just not the rows with different dates and leaving them blank instead?

 

[igold]

Does this work for you...

Private Sub PreviewBtn_Click1()

    Dim startdate As Date, enddate As Date
    Dim ws As Worksheet, REP As Worksheet, TOT As Worksheet
    Dim RTAB As Object
   
    Set REP = ThisWorkbook.Sheets("PrintReports")
    Set RTAB = REP.ListObjects("PrevReport")
    Set TOT = ThisWorkbook.Sheets("Packaged&Restock")
    Application.ScreenUpdating = False

    startdate = REP.Range("H2").Value
    enddate = REP.Range("I2").Value

    If RTAB.ListRows.Count > 1 Then
        RTAB.DataBodyRange.Delete
    End If
   
    RTAB.ListRows.Add AlwaysInsert:=True
   
     If TOT.Cells(2, 1) >= startdate And TOT.Cells(2, 1) <= enddate Then
        TOT.Range("A2:J" & TOT.Cells(Rows.Count, 1).End(xlUp).Row).Copy RTAB.DataBodyRange(1, 1)
    End If
    Application.ScreenUpdating = False
   
End Sub

 

[igold]

Please change the name of the code and remove the "1" from the end (after "Click")