Paste filtered results on the next empty row in a column

Question

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()
' 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
  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 Results on (FTP)Results and ATP Results worksheets based on true/false criteria.
 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 to copy the filtered FTP values to the Failure Report
 Set rngCopy = Sheets("Results").Range("Results_Part1").SpecialCells(xlCellTypeVisible)
 Set rngCopyNotes = Sheets("Results").Range("Results_Part2").SpecialCells(xlCellTypeVisible)

 'Set destination on the Failure Report for Copied FTP Values
  rngCopy.Copy Destination:=Sheets("Failure Report").Range("A21")
  rngCopyNotes.Copy Destination:=Sheets("Failure Report").Range("H21")

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

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

'Copy format from original header cell from Failure Report to imported headers
 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

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

    'Set Variables for source ATP Results.
 Set rngATPCopy = Sheets("ATP      Results").Range("APTResults1").SpecialCells(xlCellTypeVisible)
 Set rngATPCopyNotes = Sheets("ATP Results").Range("APTResults2").SpecialCells(xlCellTypeVisible)

 Set Sht = ThisWorkbook.Worksheets("Failure Report")
 NextRow = Sht.Range("Fail_Report_Table").Rows.Count

'Set destination for Copied Values on Failure Report
'Must be set to paste under the last occupied row (copied previously from FTP)
 rngATPCopy.Copy Destination:=Sheets("Failure Report").Range("A21")
 rngATPCopyNotes.Copy Destination:=Sheets("Failure Report").Range("H21")

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

End Sub

What's with all the commented out code? Are those things you've tried that failed? You should clean that up and and edit your question so the code is cleaner; it will make it easier to help you. Also, in general avoid .Select, as it is very limiting and forces you to make comprimises in how your code is written. — Grade 'Eh' Bacon, Oct 13 '15 at 12:22
Yes those are my prior attempts that partially worked. I am still learning the language...so it is a lot of "trial and error." I have edited out the comments (except the comments that say what each section is supposed to do). Also...why is .Select bad/limited? — Gerasimos.Zap, Oct 13 '15 at 12:32
The comments you left are okay, but they could be better if they provided more information. For example: 'Set Variables' really doesn't tell the user anything, because they can already see that those lines use the words "Set". Instead, add in something like "Set ranges to be copied" or whatever. As for .Select, I highly recommend you read through this; I found it to be an incredibly useful primer on the subject: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Grade 'Eh' Bacon, Oct 13 '15 at 12:47

Scott Holtzman · Accepted Answer · 2015-10-13T18:47:33.887

1

I think all you need to do is find the next available row for each set of copy and paste you need, then use that row as a variable of where to place the data. See the code below (notice that you do not need to use Select all the time, but can just work directly with the object itself).

Sub AdvancedFilter()
' 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

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

    'Filter ATP and FTP Results on (FTP)Results and ATP Results worksheets based on true/false criteria.
    wsFTP.Range("Results").AdvancedFilter xlFilterInPlace, Range("Criteria"), , True
    wsATP.Range("A1:I392").AdvancedFilter xlFilterInPlace, Range("Criteria"), , True

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

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

    'Copy headers from Results to Failure Report
    '### - WHY DO YOU NEED TO COPY HEADERS EACH TIME???? Isn't once sufficient???
    wsFail.Range("A" & NextRow & ":B" & NextRow).Value = wsFTP.Range("A1:B1").Value
    wsFail.Range("G" & NextRow & ":H" & NextRow).Value = wsFTP.Range("G1:H1").Value

    'Copy format from original header cell from Failure Report to imported headers
    wsFTP.Range("D1").Copy
    wsFail.Range("A" & NextRow & ":B" & NextRow).PasteSpecial xlPasteFormats
    wsFail.Range("G" & NextRow & ":H" & NextRow).PasteSpecial xlPasteFormats

    'copy ATP results to Failure Report
    Set rngCopy = wsATP.Range("ATPResults1").SpecialCells(xlCellTypeVisible)
    Set rngCopyNotes = wsATP.Range("ATPResults2").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)

End Sub

edited Oct 13 '15 at 18:47

answered Oct 13 '15 at 14:28

Scott Holtzman

27,099
5
37
72

"Run-time error 1004: Unable to get the SpecialCells property of the range class." – Gerasimos.Zap Oct 13 '15 at 14:43
Add `wsFTP.Activate` and `wsATP.Activate` statement right before setting the ranges. – Scott Holtzman Oct 13 '15 at 14:54
I am still getting the same error message. Set rngCopyNotes = wsFTP.Range("Results_Part2").SpecialCells(xCellTypeVisible) – Gerasimos.Zap Oct 13 '15 at 15:14
The new script is causing some issues with the template itself. Whenever I test the macro (even if I completely clear all values from the destination), It adds to the end of the Failure Report Destination. It keeps adding to the named range on the Failure Report every time it is run. – Gerasimos.Zap Oct 13 '15 at 15:32
make sure your named ranges are all working correctly `Results_Part1` `Results_Part1` `Criteria` etc... – Scott Holtzman Oct 13 '15 at 15:34
I figured out part of it. You had (xCellTypeVisible) and it should be (xlCellTypeVisible) and that took care of the error message, but it is still putting the filtered results below the Fail_Count_Table. – Gerasimos.Zap Oct 13 '15 at 15:44
I will edit my answer to correct the syntax mistake. `it is still putting the filtered results below the Fail_Count_Table` - isn't that what it is supposed to do? – Scott Holtzman Oct 13 '15 at 15:57
I intended to paste the FTP results in row 22 and the ATP results in the next open cell after the copied FTP results. What is happening is the FTP and ATP results are being sent to the cell immediately after the Fail_Report_Table. – Gerasimos.Zap Oct 13 '15 at 16:02
I was able to get the FTP to copy to the beginning of Fail_Report_Table (named range), but the ATP still copies in the row directly after the Fail_Report_Table ends. – Gerasimos.Zap Oct 13 '15 at 16:14
so each time it starts from the first cell in the `Fail_Report_Table` range and copys the FTP results, then you want to copy the ATP results directly under the last FTP result? If so, will the ATP result overwrite any existing rows that may be there? Or will it always be blank? – Scott Holtzman Oct 13 '15 at 16:26
Yes, to paste directly under the last row of results for FTP. Also, the ATP results will not overwrite any existing rows. Until the report is populated, the cells will be empty in the Fail_Report_Table. – Gerasimos.Zap Oct 13 '15 at 16:31
I edited my code to adjust the 2nd instance of finding `NextRow`. Please see that edit. – Scott Holtzman Oct 13 '15 at 16:41
I made the changes, but it now copies the ATP results first, skips 11 rows, and then enters the FTP results. – Gerasimos.Zap Oct 13 '15 at 17:07
make sure your the 'Fail_Report_Table` is empty before running the code ... as it would be in your initial run. – Scott Holtzman Oct 13 '15 at 17:20
The table is empty and it is still adding the FTP results after the last cell in the Fail_Report_Table. And the number of cells in between the ATP and FTP results depends on the number of empty rows in the table. And it is still adding the ATP first. Is there another way to go about this? – Gerasimos.Zap Oct 13 '15 at 17:27
it's hard without seeing the datasheet, but I edited the initial `NextRow` syntax. If that doesn't work, why don't you step through your code line by line and see where it goes wrong after evaluating each line's action against your workbook. – Scott Holtzman Oct 13 '15 at 17:36
The ATP results are overwriting the FTP results. The FTP works fine, but when it gets time to copy the ATP results...it sends it to the first cell of the Fail_Report_Table. And is there any way to show what is going on rather than simply explaining it? – Gerasimos.Zap Oct 13 '15 at 17:51
I made one more edit to the last `NextRow`. Sorry, I assumed that `Fail_Report_Table` started in cell A1. Maybe that was wrong. – Scott Holtzman Oct 13 '15 at 18:48
Not a problem. The Fail_Report_Table starts in A22 (A21 if you include headers). Thank you for taking the time to get it right. And I got it correct (and added a line to clear the Fail_Report_Table so it does not stack results if the button is pressed multiple times. – Gerasimos.Zap Oct 13 '15 at 18:53
How difficult would it be to add a third Sheet to the mix? I attempted to add it to the macro and it said I had 25 failures when my sums tell me that I have 40. Is it as simple as using and modifying some of the code from above? – Gerasimos.Zap Oct 13 '15 at 19:49
It should be as simple as that, without seeing your data. Give it a try, if you have trouble post a new question and the SO community can help you. – Scott Holtzman Oct 13 '15 at 20:07

Paste filtered results on the next empty row in a column

1 Answers1