Paste Filtered Results on the Next Empty Row in a Column

[Gerasimos_Zap]

I am building a custom template for technicians to use while on the production floor. What I am attempting to do is filter the results on separate sheets (FTP Results and ATP Results), copy those results to the next empty cell in a specific column in another sheet (Failure Report). I have both the FTP Results and ATP Results as named ranges (Results and APTResults respectively) as is the Failure Report (Fail_Report_Table). I need to paste the first two columns of the FTP Results/ATP Results sheets into the first two columns of the Fail_Report_Table(A22:B22) and then the last two columns and paste into the last two columns of Fail_Report_Table (H22:I22).


As for what I have right now, I can get it to work when only pulling from one sheet, but not both. I can get it to apply the advanced filter to both sheets, but it will only copy the results from ATP Results. I need to paste the filtered results from FTP Results first, find the next available cell in Columns A and H, then paste filtered results from ATP Results at that point. The number of filtered values will vary, so the solution has to be dynamic. I am relatively new to VBA and my code is a bit of a jumbled mess (and I am fairly sure that is part of the problem).

    Sub AdvancedFilter()
    ' AdvancedFilter Macro


     'Declare Variables
      Dim rngCopy As Range
      Dim rngCopyNotes As Range
      Dim rngCopyFailCT As Range
     '
      Dim rngATPCopy As Range
      Dim rngATPCopyNotes As Range
      Dim rngATPCopyFailCT As Range


      Dim NextRow As Long
      Dim Sht As Worksheet


    'Filter ATP and FTP
     Sheets("Results").Select
     Range("Results").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("Criteria"), Unique:=True
        
    Sheets("ATP Results").Select
    Range("A1:I392").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("APTCriteria"), Unique:=False


    Sheets("Results").Activate
    'Set Variables
     Set rngCopy = Sheets("Results").Range("Results_Part1").SpecialCells(xlCellTypeVisible)
     Set rngCopyNotes = Sheets("Results").Range("Results_Part2").SpecialCells(xlCellTypeVisible)
     'Set rngCopyFailCT = Sheets("Results").Range("H3:H962").SpecialCells(xlCellTypeVisible)
        
     'Set destination for Copied Values
      rngCopy.Copy Destination:=Sheets("Failure Report").Range("A21")
      rngCopyNotes.Copy Destination:=Sheets("Failure Report").Range("H21")
     'rngCopyFailCT.Copy Destination:=Sheets("Failure Report").Range("C22")


    'Copy headers from Results to Failure Report
     Sheets("Results").Activate
     Range("A1:B1").Select
     Selection.Copy
     Sheets("Failure Report").Select
     Range("A21:B21").PasteSpecial 'Paste:=xlPasteFormat


     Sheets("Results").Activate
     Range("G1,H1").Select '("J2:I2")
     Selection.Copy
     Sheets("Failure Report").Select
     Range("H21:I21").PasteSpecial 'Paste:=xlPasteFormat


    'Sheets("Results").Activate
    'Range("H2").Select
    'Selection.Copy
    'Sheets("Failure Report").Select
    'Range("C21").PasteSpecial


    'Copy format from original header cell from Failure Report
     Range("D21").Select
     Selection.Copy
     Range("A21:B21").Select ' note that we select the whole merged cell
     Selection.PasteSpecial Paste:=xlPasteFormats


     Range("D21").Select
     Selection.Copy
     Range("H21:I21").Select ' note that we select the whole merged cell
     Selection.PasteSpecial Paste:=xlPasteFormats


    'Autofit columns A through H
    'Columns("A:H").EntireColumn.AutoFit


     Range("F12").Select
     Sheets("Results").Activate
     Application.CutCopyMode = False
     Range("N34").Select
     Sheets("Failure Report").Activate


        'Set Variables
     Set rngATPCopy = Sheets("ATP      Results").Range("APTResults1").SpecialCells(xlCellTypeVisible)
     Set rngATPCopyNotes = Sheets("ATP Results").Range("APTResults2").SpecialCells(xlCellTypeVisible)
    'Set rngCopyFailCT = Sheets("Results").Range("H3:H962").SpecialCells(xlCellTypeVisible)
     Set Sht = ThisWorkbook.Worksheets("Failure Report")
     NextRow = Sht.Range("Fail_Report_Table").Rows.Count
    'Set destination for Copied Values
     rngATPCopy.Copy Destination:=Sheets("Failure Report").Range("A21")
     rngATPCopyNotes.Copy Destination:=Sheets("Failure Report").Range("H21")
    'rngCopyFailCT.Copy Destination:=Sheets("Failure Report").Range("C22")
    'NextRow = Sheets("Failure Report").Range("FailReportTable" & Rows.Count).End(xlUp).Row + 1


    'Sheets("Results").Activate
    'Range("H2").Select
    'Selection.Copy
    'Sheets("Failure Report").Select
    'Range("C21").PasteSpecial


     Range("F12").Select
     Sheets("ATP Results").Activate
     Application.CutCopyMode = False
     Range("N34").Select
    'Range("Results").Select


    'ActiveSheet.ShowAllData
    'Range("N34").Select
    'Sheets("Failure Report").Activate
    End Sub