Copying filtered data to another sheet

[beba420]

Hi All

Long story short im throwing together a mini payroll system for my mates startup. Working on a macro to copy some data into a template ive set up on a sheet, it then saves a pdf to a folder and attaches it as an email to his employee

Ive set up a page ("SessionInfo") that has a tabel with information on all the "Sessions" my friend sends his employees on (employees consist of himself and a few of his friends, all therapists who go out and meet at their clients homes). Things like empID, date, client, cost, comission, etc. Macro filters this table according to which empID and date range specified in another table ("ControlPanel"). It then copies all the data it needs to into my template sheet ("Invoices") and creates the pdf

ive got it all working, however my problem is in my copy function. Ive set it up to copy data from columns B to I (in sheet "SessionInfo", row 1 is the column headings). It copies from row2 of those columns to the last row that has data in it. I thought that once i applied the filter, the copy paste function would automatically adjust and only copied the filtered data... it did not and just copies the whole table

is there a way just to copy the filtered data of those rows (without column headings)? Would appreciate any help

Below is the code im using to copy and paste my data, this si what ill need to replace

'copy data from sessionfo to draft
    LastRow = Worksheets("SessionInfo").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    Worksheets("SessionInfo").Range("B2:I" & LastRow).Copy
    
    Worksheets("Invoices").Range("B6").PasteSpecial Paste:=xlPasteValues

and here is the whole macro for context

    Dim eDate As String
    Dim lDate As String
    Dim empID As Integer
    Dim empName As String
    Dim empEmail As String
    Dim LastRow As Integer
    
    Dim folderPath As String
    Dim fileName As String
    Dim filePath As String
    
Sub InvSummary()
    eDate = Sheets("ControlPanel").Range("B5")
    lDate = Sheets("ControlPanel").Range("B6")
    empID = Sheets("ControlPanel").Range("B7").Value
    empName = Sheets("ControlPanel").Range("B8").Value
    empEmail = Sheets("ControlPanel").Range("B9").Value
    
    
    folderPath = Sheets("ControlPanel").Range("B11").Value
    fileName = Sheets("ControlPanel").Range("B12").Value
    filePath = folderPath & fileName
    
    FilterInvoices
    SortTableValue
    ClearInvoice
    
    'copy data from sessionfo to draft
    LastRow = Worksheets("SessionInfo").Range("a1").SpecialCells(xlCellTypeLastCell).Row
    Worksheets("SessionInfo").Range("B2:I" & LastRow).Copy
    
    Worksheets("Invoices").Range("B6").PasteSpecial Paste:=xlPasteValues
    Worksheets("Invoices").Range("C3") = empName
    Worksheets("Invoices").Range("G3") = eDate
    Worksheets("Invoices").Range("I3") = lDate
    
    Dim currow As String
    Dim subrow As String
    
    
    'add sum to bottom of draft
     LastRow = Worksheets("Invoices").Range("H1").SpecialCells(xlCellTypeLastCell).Row
     subrow = LastRow + 2
     
     Worksheets("invoices").Range("G" & subrow).Formula = "=sum(G6:G" & LastRow & ")"
     Worksheets("invoices").Range("H" & subrow).Formula = "=sum(H6:H" & LastRow & ")"
     Worksheets("invoices").Range("I" & subrow).Formula = "=sum(I6:I" & LastRow & ")"
     
     Worksheets("invoices").Range("G6:I" & subrow).NumberFormat = "$#,##0.00"
    'create pdf
    Worksheets("invoices").ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath
    
    'clearInvoice
    ClearSessFilters
    
    'create new email and attach pdf
    
End Sub

and here are the trhee other macros used to filter and sort the table, as well as clear the invoice template so its ready for a new one.... i dont think theyll be required but figured id add them as they are referened in the main invSummary macro. just in case

Sub ClearInvoice()
    Dim LastRow As Integer
    LastRow = Worksheets("Invoices").Range("I1").SpecialCells(xlCellTypeLastCell).Row
    
    'clears page
    Worksheets("Invoices").Range("B6:I" & LastRow).ClearContents
    Worksheets("Invoices").Range("C3") = "(NAME)"
    Worksheets("Invoices").Range("G3") = "(DATE1)"
    Worksheets("Invoices").Range("I3") = "(DATE2)"
End Sub

Sub FilterInvoices()
    Dim fil1 As String
    Dim fil2 As String
    Dim date1 As Date
    Dim date2 As Date
    
    
    eDate = Sheets("ControlPanel").Range("B5")
    lDate = Sheets("ControlPanel").Range("B6")
    empID = Sheets("ControlPanel").Range("B7").Value
    
    date1 = Format(eDate, "MM/DD/YYYY")
    date2 = Format(lDate, "MM/DD/YYYY")
    fil1 = ">=" & eDate
    fil2 = "<=" & lDate
    
    Worksheets("SessionInfo").Activate
    If ActiveSheet.AutoFilterMode Then
        ClearSessFilters
    End If
    
    ActiveSheet.ListObjects("SessInfo").Range.AutoFilter Field:=1, Criteria1:=empID
    ActiveSheet.ListObjects("SessInfo").Range.AutoFilter Field:=2, Criteria1:=">=" & date1, Operator:=xlAnd, Criteria2:="<=" & date2
End Sub
Sub SortTableValue()

    Worksheets("SessionInfo").Activate
    
    Dim iSheet As Worksheet
    Dim iTable As ListObject
    Dim iColumn As Range
    Set iSheet = ActiveSheet
    Set iTable = iSheet.ListObjects("SessInfo")
    Set iColumn = Range("SessInfo[Date]")
    With iTable.Sort
    .SortFields.Clear
    .SortFields.Add Key:=iColumn, SortOn:=xlSortOnValues, Order:=xlAscending
    .Header = xlYes
    .Apply
    End With
End Sub

Sub ClearSessFilters()
ActiveWorkbook.Worksheets("SessionInfo").ListObjects("SessInfo").Sort. _
        SortFields.Clear
        ActiveSheet.ShowAllData
End Sub