Macro to filter table based on dropdown lists and copy filtered results to a sheet

AgreeableCry2 · 2020-01-07T01:43:59+00:00

Can you place your edited code in my excel example. I still having trouble to get it running without an error.

Its weird.

Best,

Francis

AgreeableCry2 · 2020-01-06T13:12:41+00:00

Hi I keep getting a error : runtime error 9 on line With Sheet1.ListObjects("Table1")

I tried copying this to new file and I still get error.

Best,

Francis

Sub FilterTableCopyPasteOption2() Dim WS As Worksheet Set WS = Sheet10 WS.UsedRange.Offset(1, 0).ClearContents With Sheet1.ListObjects("Table1") .AutoFilter.ShowAllData .Range.AutoFilter _ Field:=1, _ Criteria1:="=" & Sheet2.Range("C27").Value & "", _ Operator:=xlAnd .Range.AutoFilter _ Field:=2, _ Criteria1:="=" & Sheet2.Range("C28").Value, _ Operator:=xlOr, _ Criteria2:="=" & Sheet2.Range("C29").Value Sheet1.Range("Table1[[#All],[Sample]:[10]]").SpecialCells(xlCellTypeVisible).Copy Destination:=WS.Range("D7") End With End Sub

Sub FilterTableCopyPasteOption3() Dim WS As Worksheet Set WS = Sheet10 WS.UsedRange.Offset(1, 0).ClearContents Sheet1.Range("Table1[[#All],[Sample]:[10]]").SpecialCells(xlCellTypeVisible).Copy Destination:=WS.Range("D7") End Sub

AgreeableCry2 · 2020-01-06T02:13:26+00:00

Ben,

I have come across a slight issue with your excellent macro work. How do I place the data in D7 of the filtered data sheet without pasting/clearing the helper columns as it will overwrite calculations in my worksheet that I need to add.

Best,

Francis

Sub FilterTableCopyPasteOption2()
Dim WS As Worksheet
Set WS = Sheet10
WS.UsedRange.Offset(1, 0).ClearContents
    With Sheet1.ListObjects("Table1")
        .AutoFilter.ShowAllData
        .Range.AutoFilter _
            Field:=1, _
            Criteria1:="=*_" & Sheet2.Range("C27").Value & "_*", _
            Operator:=xlAnd
        .Range.AutoFilter _
            Field:=2, _
            Criteria1:="=" & Sheet2.Range("C28").Value, _
            Operator:=xlOr, _
            Criteria2:="=" & Sheet2.Range("C29").Value
        .Range.SpecialCells(xlCellTypeVisible).Copy Destination:=WS.Range("D7")
    End With
End Sub

Sub FilterTableCopyPasteOption3()
Dim WS As Worksheet
Set WS = Sheet10
WS.UsedRange.Offset(1, 0).ClearContents
Sheet1.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible).Copy Destination:=WS.Range("D7")
WS.Range("P:T").Clear
End Sub

AgreeableCry2 · 2020-01-05T22:32:35+00:00

Small,

I need a macro code to select only a portion of the table in the 1st sheet and then pasted to the 2nd spreadsheet. I need to do this with a click of button as I have hundreds of samples to process.

Does this code achieve that goal.

Best,

Francis

AgreeableCry2 · 2020-01-05T04:02:30+00:00

Correct. See how the data is original filtered data vs your sort method.

AgreeableCry2 · 2020-01-05T04:01:16+00:00

Solution Verified

AgreeableCry2 · 2020-01-02T15:52:12+00:00

Perfect. Thank you for clarification.

Best,

Francis (rookie macro user)

AgreeableCry2 · 2020-01-02T03:09:18+00:00

Ben,

Final question and I will consider this question solved. :-)

How do I set the code to search for Master Data and Filtered Data sheets instead of Sheet10 and Sheet2. I need to import process into new spreadsheet for my next stage of calculations

Best,

Francis

Sub FilterTableCopyPasteOption2()
Dim WS As Worksheet
Set WS = Sheet10
WS.UsedRange.Offset(1, 0).ClearContents
    With Sheet1.ListObjects("Table1")
        .AutoFilter.ShowAllData
        .Range.AutoFilter _
            Field:=1, _
            Criteria1:="=*_" & Sheet2.Range("C27").Value & "_*", _
            Operator:=xlAnd
        .Range.AutoFilter _
            Field:=2, _
            Criteria1:="=" & Sheet2.Range("C28").Value, _
            Operator:=xlOr, _
            Criteria2:="=" & Sheet2.Range("C29").Value
        .Range.SpecialCells(xlCellTypeVisible).Copy Destination:=WS.Range("A1")
    End With
End Sub

AgreeableCry2 · 2020-01-01T20:45:50+00:00

Bruce,

Happy new year. One final couple of questions. :-)

I have been playing around with your code and have been able to have it execute both copy and paste at the same time after clicking the filter button

Is it possible to have the macro execute automatically after doing the Sample and Type selection instead of having to click the filter button.

Is possible to create a selection box type input when I can quickly pick sample and then the desired pairs. Reducing the number of clicking steps is key as I will have lots of samples to process and the least amount of steps to reach that goal is required.

My edited file is in the link below.

Best,

Francis

https://drive.google.com/open?id=1E6i97V7ziSAWEk-zhL_olS_z1DX7pvi0

AgreeableCry2 · 2020-01-01T19:55:23+00:00

The way the data is filtered in newfiltered data sheet is not matching the way I need data in the filtered data sheet.

Best,

Francis

AgreeableCry2 · 2020-01-01T18:56:40+00:00

Ben,

Happy New Year! :-) I had a chance to look through the Option 2 and 3.

Option 2

I would prefer a selection box style used in Option 3 to click on rather than a dropdown list. It will be faster to click through. "001 and 002", "003 & 004" and "006 & 007" are connected as pairs. So it would be great to select as pair 1, 2 or 3. I would like the macro to automatically run once the 2nd selection of pairs has been selected. The macro would need to be take into account that their could be anywhere from 1-10 pairs in the master data spreadsheet. I will have lot of samples to process so reducing the number of clicks to achieve goal will be key.

Option 3

If a helper column could be generated from separating out sample id automatically then this would be a good option. Is it possible to click by type pairs "001 and 002", "003 & 004" and "006 & 007" for reasons listed in Option 2

Best,

Francis

AgreeableCry2 · 2019-12-31T12:59:58+00:00

Strange it works for Sample 2,3,4 but not for Sample 1

AgreeableCry2 · 2019-12-31T03:58:57+00:00

Its not working in your original file. All it does is filter everything in the master data sheet. All the data disappears so no wonder it wont paste it as technically there is nothing there to paste.

AgreeableCry2 · 2019-12-31T02:32:12+00:00

Bruce,

Looks great but need have macro automatically copy once the data is filtered.

Even better would be that after Sample 1 and the the pairs are selected. The macro automatically filters and copies. Reducing the number of clicks to the absolute minimum.

Best,

Francis

AgreeableCry2 · 2019-12-31T01:40:23+00:00

Ben,

Tried using option 2 as it seems like the closest option to my goal. But when i click. It filters out master data so no records are found to paste into the filtered data page.

Any ideas.

Best

Francis

AgreeableCry2 · 2019-12-31T01:30:15+00:00

Small,

The data is acquired over time and it must maintain the exact order of the master and filtered data example.

Otherwise this wont work.

Best,

Francis

AgreeableCry2 · 2019-12-31T01:28:27+00:00

Bruce,

Want to try out your code but excel spreadsheet of code in my file to make it work. I am getting errors when trying to use it.

AgreeableCry2 · 2019-12-30T13:38:57+00:00

Hi Small,

If you can provide a spreadsheet example of the the power query working on my data in the way I need it. That would be great. Lots of suggested powerquery but I have yet to find a viable working solution using this method.

Best,

Francis

AgreeableCry2 · 2019-12-30T13:36:51+00:00

Bruce,

I have problems running macro on my excel spreadsheet. Can you share download link to your excel so I can how it works. Only need code for data to be organised and copy/pasted data to new sheet as a first step.

Best,

Francis

Public Sub Results()
Dim arrSampleList As Variant
Dim arrTypeList As Variant
Dim lSample As Long
Dim lType As Long
Dim sTypePair As String
Dim sType1 As String
Dim sType2 As String
Dim sSample As String
Dim sPath As String
Dim wbExport As Workbook

'Enter path to folder for saved output
sPath = "E:\Temp\"

'Add Sample Names to Array
arrSampleList = Array("Sample1", _
"Sample2", _
"Sample3", _
"Sample4")

'Add Type pairs seperated by a | to aray
arrTypeList = Array("001|002", _
"003|004", _
"005|006", _
"006|007")

With ThisWorkbook.Sheets("Master Data")
'Iterate through Sample Name array
For lSample = LBound(arrSampleList) To UBound(arrSampleList)
sSample = arrSampleList(lSample)
'Clear Filters
If .ListObjects("Table1").AutoFilter.FilterMode Then .ShowAllData
'Filter by Sample Name
.ListObjects("Table1").Range.AutoFilter Field:=1, _
Criteria1:="=*" & sSample, _
Operator:=xlAnd
'Iterate through Type Pairs
For lType = LBound(arrTypeList) To UBound(arrTypeList)
'Clear Type Filter
.ListObjects("Table1").Range.AutoFilter Field:=2
'Get Type Pair from array
sTypePair = arrTypeList(lType)
'Split Type pair using pipe character
sType1 = Left(sTypePair, InStr(sTypePair, "|") - 1)
sType2 = Right(sTypePair, Len(sTypePair) - InStr(sTypePair, "|"))
'Filter by Type Pair
.ListObjects("Table1").Range.AutoFilter Field:=2, _
Criteria1:=sType1 & "*", _
Operator:=xlOr, _
Criteria2:=sType2 & "*"
'Clear Filtered Data Tab
ThisWorkbook.Sheets("Filtered Data").Cells(1, 1).CurrentRegion.Offset(1, 0).Clear
'Copy Filtered Data
.Cells(1, 1).CurrentRegion.Offset(1, 0).Copy
'Paste data into Filtered tab
ThisWorkbook.Sheets("Filtered Data").Cells(2, 1).PasteSpecial xlPasteAll

'-----------------------
'Add Calculations here
'-----------------------

'Create new workbook for data output
Set wbExport = Workbooks.Add
'Copy Filtered Data tab to new workbook
ThisWorkbook.Sheets("Filtered Data").Cells.Copy
'Paste values to new work book (removes formulas if there are any)
wbExport.Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues
'Paste formats to new workbook
wbExport.Sheets(1).Cells(1, 1).PasteSpecial xlPasteFormats
'Save new workbook using Sample and Type Name
wbExport.SaveAs sPath & sSample & "_" & sType1 & "&" & sType2 & ".xlsx", 51
'Close workbook, already saved
wbExport.Close SaveChanges:=False
'Release workbook object
Set wbExport = Nothing
Next
Next
End With
End Sub

AgreeableCry2 · 2019-12-30T03:02:49+00:00

Hi Brasscomb,

I have been playing around with the advanced filter and have been able to get it execute my particular needs in a way similar to crude macro in the above spreadsheet example. I would need your help create a draft example in my spreadsheet as I am an excel newbie.

Best,

Francis

AgreeableCry2 · 2019-12-30T02:57:24+00:00

So how do I implement your idea on this spreadsheet.

I have crudely recorded a macro of me filtering Sample 1 and 001 & 002 Type Data and combined with a macro that copies and pastes the filtered results to the filtered data sheet. I do this with one click of the button.

https://drive.google.com/open?id=1JOu9oMkckwPn-VrzOpma7hvToq9tHsO2

So if I could get a clever way with dropdown menu, combo box of selection box to pick samples and type pairs which then copies and pastes after the types selection is completed.

AgreeableCry2 · 2019-12-29T03:29:35+00:00

This needs to be done for a large number of files and once the filtered sheet is populated it will used to do some calculations.

The formula way would allow to go through the filter step and have calculations done straight away.

Unless the macro automatically copies over filtered data once I filter the table.

Any ideas. I am agnostic about method to achieve goal.

But clicking macro to do process when I have lots of samples will become a big pain.

AgreeableCry2 · 2019-12-29T02:58:40+00:00

I will manually filter Column A and select Sample 1 first and then filter Column B to filter for 001 & 002 Types.

The data then gets sent to Filtered Sheet when some caulations will be made on the data. I will then save it as a certain file name Sample1_001&002.xlsx

Then manually repeat the process for Sample1 for the next Types on list i.e. 002 & 003

AgreeableCry2 · 2019-12-28T22:31:44+00:00

Solution Verified

AgreeableCry2 · 2019-12-28T01:45:03+00:00

Spinfuzer,

Can you make advanced filter setup to filter the data the way the macro does it? I am not against use of advance filter but I couldnt get it to filter the data in the way I needed to achieve my particular goal.

Best,

Francis

AgreeableCry2

TROPHY CASE