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

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 :help 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.

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.

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

Code:

---

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

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.

Quote:

---

Originally Posted by 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.

---

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.

Quote:

---

Originally Posted by 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

Code:

---

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

---