Need VBA to create inputbox for multiple values & filter multiple columns

[JOEYSCLEE]

Hi, there
I have the excel file with 51 columns & over 600000 rows. I need to filter out specific values with multiple columns in order to do pivot table later.

As there will be using different headers for sorting out specific / multiple value every time, would you please to create VBA code with input box function (the comma) for selecting multiple headers of requested columns simultaneously? Also, appreciate that the specific / multiple values can be input with the comma for filtering requested data as well.

Please note that the testing file only got 70 rows for your reference.

[SamT]

just a suggestion, but I would create a UserForm that had say 6 to 10 of the most commonly used filterings ready-made for use with a single click, then have a option of "Custom Filtering."

However, I don't use Pivot Tables,. Somebody else will be here soon with a better idea.

[Paul_Hossler]

An alternate way would be to use the built in data filtering (or advanced data filtering) and then when you have only the data needed, copy to a new sheet and run the PT off of that

Option Explicit
'https://excel.tips.net/T002914_Limits_to_Filtering.html

Sub Macro2()
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Reporting").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Worksheets.Add.Name = "Reporting"
    
    'with data filtered
    Worksheets("DataDataData").Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Worksheets("Reporting").Range("A1")
    Worksheets("Reporting").Range("A1").CurrentRegion.EntireColumn.AutoFit
End Sub

The macro could be expanded to allow user to enter name of report sheet and automatically make the PT onto a third sheet, format, etc.

It's possible to get the names of the filter columns and the value(s) each is filtered on and make a report title

[JOEYSCLEE]

Hi, SamT
Thanks for your suggestion! Could you please advise the link/code how to create UserForm for Custom Filtering? I have no idea about the UserForm.

just a suggestion, but I would create a UserForm that had say 6 to 10 of the most commonly used filterings ready-made for use with a single click, then have a option of "Custom Filtering."

However, I don't use Pivot Tables,. Somebody else will be here soon with a better idea.

[JOEYSCLEE]

Hi, Paul
Thanks for the reviewing!! Noticed that the data of the Tab - Reporting which was copied the filtered data from the original worksheet. Your advised code is great for creating pivot table with the requested data.

An alternate way would be to use the built in data filtering (or advanced data filtering) and then when you have only the data needed, copy to a new sheet and run the PT off of that

Option Explicit
'https://excel.tips.net/T002914_Limits_to_Filtering.html

Sub Macro2()
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Reporting").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Worksheets.Add.Name = "Reporting"
    
    'with data filtered
    Worksheets("DataDataData").Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Worksheets("Reporting").Range("A1")
    Worksheets("Reporting").Range("A1").CurrentRegion.EntireColumn.AutoFit
End Sub

The macro could be expanded to allow user to enter name of report sheet and automatically make the PT onto a third sheet, format, etc.

It's possible to get the names of the filter columns and the value(s) each is filtered on and make a report title