Paste filtered results to another sheet

Question

I am building a template that has three procedure worksheets(FTP, ATP, CS) and a Failure Report worksheet. What I am attempting to do is apply an advanced filter to each of those procedure sheets, copy only the filtered results, and paste them in succession in a named range on the Failure Report.

I have the advanced filter working properly, however it causes issues when I attempt to copy the results into the Failure Report. The FTP results will paste correctly, however the ATP and CS will paste to the bottom of the named range (and expand the range). I need to tell it to paste in the next empty cell in Column A (within the named range). I have attached a copy of the results that are returned.

Sub AdvancedFilterCopyAttempt()
' Script to apply an advanced filter to multiple worksheets and copy those results to copy to the Failure Report.

 'Declare Variables
Dim rngCopy As Range, rngCopyNotes As Range
Dim NextRow As Long
Dim wsFTP As Worksheet, wsATP As Worksheet, wsFail As Worksheet, wsCS As Worksheet

Set wsFTP = Sheets("Results")
Set wsATP = Sheets("ATP Results")
Set wsFail = Sheets("Failure Report")
Set wsCS = Sheets("CS Results")

 Sheets("Results").Activate
 Range("Results").AdvancedFilter Action:=xlFilterInPlace, _
 CriteriaRange:=Range("Criteria"), Unique:=False

Sheets("ATP Results").Activate
Range("A:I").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("APTCriteria"), Unique:=True

Sheets("CS Results").Activate
Range("A:I").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("CSCriteria"), Unique:=True


wsFTP.Activate
'copy FTP results to Failure Report
Set rngCopy = wsFTP.Range("Results_Part1").SpecialCells(xlCellTypeVisible)
Set rngCopyNotes =      wsFTP.Range("Results_Part2").SpecialCells(xlCellTypeVisible)

Sheets("Failure Report").Range("A:A").ClearContents
NextRow = wsFail.Range("Fail_Report_Table").Cells(1, 1).Row
rngCopy.Copy wsFail.Range("A" & NextRow)
rngCopyNotes.Copy wsFail.Range("H" & NextRow)


wsATP.Activate
'copy ATP results to Failure Report
Set rngCopy = wsATP.Range("APTResults1").SpecialCells(xlCellTypeVisible)
Set rngCopyNotes = wsATP.Range("APTResults2").SpecialCells(xlCellTypeVisible)

NextRow = wsFail.Range("Fail_Report_Table").Cells(1, 1).End(xlDown).Offset(1).Row
rngCopy.Copy wsFail.Range("A" & NextRow)
rngCopyNotes.Copy wsFail.Range("H" & NextRow)

wsCS.Activate
Set rngCopy = wsCS.Range("CSResults1").SpecialCells(xlCellTypeVisible)
Set rngCopyNotes = wsCS.Range("CSResults2").SpecialCells(xlCellTypeVisible)

NextRow = wsFail.Range("Fail_Report_Table").Cells(1, 1).End(xlDown).Offset(1).Row
rngCopy.Copy wsFail.Range("A" & NextRow)
rngCopyNotes.Copy wsFail.Range("H" & NextRow)

Sheets("Failure Report").Activate
End Sub

is row 21 always going to be a header row? or does it get pasted in with the FTP Results command? Maybe a better question is does Fail_Report_Table always begin at row 21? If its another row, what is it? — Scott Holtzman
Yes, row 21 will always be headers. The data starts at cell A22. Fail_Report_Table spans from A22:I47 with headers from A21:I21. — Gerasimos.Zap

Scott Holtzman Scott Holtzman · Accepted Answer · 2015-10-14T18:15:43

Okay, now that I understand that Fail_Report_Table starts at A22, change the line

NextRow = wsFail.Range("Fail_Report_Table").Cells(1, 1).End(xlDown).Offset(1).Row

to

NextRow = wsFail.Range("Fail_Report_Table").Cells(1, 1).Offset(-1).End(xlDown).Offset(1).Row

For both times it occurs.

Note: I know you have been working on this for quite a bit, so it would really serve you to study why these changes are taking place, instead of just copying them into your code and moving on, so you really understand what it is doing, so you can replicate it in the future, or manipulate it as needed.

Paste filtered results to another sheet

1 Answers