How to drag and drop multiple filters at once in a pivot table?

[Mirisage]

Hello Excel Forum,
I have the attachced pivot table.

I wanted to drag and drop fileds "scrub01 to "scrub32" into the "Report Filter" panel.
I have manually dragged and dropped one by one.

Q:
Of course I can do this, but would like to ask if there are any efficinet way to drag and drop all the fileds "scrub01 to "scrub32" into the "Report Filter" panel at once.

Thanks for the help.
Mirisa

[Andy Pope]

I don't think you can multiselect fields.

this 1 liner in the immediate window will do it.

for i=1 to 32:ActiveSheet.PivotTables("PivotTable2").PivotFields("scrub" & format(i,"00")).Orientation = xlPageField:ActiveSheet.PivotTables("PivotTable2").PivotFields("scrub" & format(i,"00")).position=1:next i

[Mirisage]

Hello Andy Pope,

Thank you very much for this (I think) excel macro statement.

for i=1 to 32:ActiveSheet.PivotTables("PivotTable2").PivotFields("scrub" & format(i,"00")).Orientation = xlPageField:ActiveSheet.PivotTables("PivotTable2").PivotFields("scrub" & format(i,"00")).position=1:next i

Given my basic knowledge could I ask how to implement this statement in the "Book7.xlsx" file?

Should I re-type above statement in somewhere in the "Book7.xlsx" file?

Thanks
Mirisa

[Andy Pope]

If you need to do it more that once you could add it to a macro in your workbook.

I was suggesting you simply copy and paste the line into the immediate window of VBE and execute it.
With your workbook open use ALT+F11 to open VBE. CTRL+G to open Immediate window. Paste in line and with the cursor on the line press enter to execute the instruction.

[oeldere]

Another approach

Rearange the data in your file, with this macro.

After that you will be able to add the subcat. at once.

See the attached file.

I did not update the pivot table (make a new pivot table).

Dim rsht1 As Long, rsht2 As Long, i As Long, Col As Long, wsTest As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

   
   With Sheets("Output")
    .UsedRange.ClearContents
    .Range("A1:E1").Value = Array("scrubbed", "date", "category", "Sub", "Value")

    End With
    
    
    rsht1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    rsht2 = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
    Col = 4
    
    For i = 2 To rsht1
            Do While Sheets("sheet1").Cells(1, Col).Value <> ""
            rsht2 = rsht2 + 1
           
            Sheets("Output").Range("A" & rsht2).Value = Sheets("sheet1").Range("A" & i).Value
            
            Sheets("Output").Range("B" & rsht2).Value = Sheets("sheet1").Range("B" & i).Value
            
            Sheets("Output").Range("C" & rsht2).Value = Sheets("sheet1").Range("C" & i).Value
            
            Sheets("Output").Range("D" & rsht2).Value = Sheets("sheet1").Cells(1, Col).Value
            
            Sheets("Output").Range("E" & rsht2).Value = Sheets("sheet1").Cells(i, Col).Value
            
            
     
            Col = Col + 1
        Loop
        Col = 4
    
    Next
  With Sheets("Output")
 
 ' .Range("E2:E" & .Rows.Count).SpecialCells(4).EntireRow.Delete
   
   Columns("A:Z").EntireColumn.AutoFit
    End With

[Mirisage]

Hi Andy Pope and Oeldere,
Many thanks to both of you for this expert knowledge.

Regards
Mirisa

[Shmear]

Hi All
I have a book that has several (some linked) spreadsheets... Recently some of the spreadsheets formulas were/have been converted to date format. I have changed them back to general format & saved. When re-opening this
book some of the cells formats have been converted back to "date" format.... This is getting frustrating. Can someone help me please

Regards

Mike