Use VBA to filter specific column and delete filter rows on new "Show Details" worksheet generated by pivot table

[Jayrile]

Hi,

I am new to this forum and I consider myself to be a beginner to Excel VBA.

I have a pivot table and what I would like to know is when clicking on a value in the pivot table, when the new "Show Details" sheet opens, I would like via VBA to accomplish the following in the new "Show Details" sheet (I would like this to happen when the sheet is created):

• delete all rows that contain the value "Regular" in the column named "WT"
• delete entire columns of data that have a specific header name (e.g. delete columns with headers "Department", "Location", and "Wages".

Hope someone can help me out...I've been at a loss for the last couple of days.

Thank you!

 

[Jayrile]

Looks like I might have the first part figured out. Can I get help with the 2nd question:

How to delete columns of data that have a specific header name? I found some code and am trying to tweak it, it only works for me when I run it from Worksheet, not Workbook. I'm trying to implement it within the Workbook (New Sheet). I need the code to work on the new "Show Details" sheet, which is the active sheet.


Set MR = ActiveWorkbook.ActiveSheet.Range("A1:AE1") ' setting the range to the header row of the worksheet

'Checking all the header names in the first row of the worksheet, and deleting all columns with header name "Department"
For Each cell In MR
If cell.Value = "Department" Then cell.EntireColumn.Delete (here I get the error "variable not defined" on cell.value
Next