Ganesh Vijaykumar
asked on
Selecting all the check boxes excluding #N/A and Blank under Row labels in a pivot table
Hi,
My source data has empty cells and #N/A. When I Pivot Table this data - the Pivot Table shows "(blank)" for the empty cells and #N/A under Row labels. I would not want to manually filter and uncheck (blank) and #N/A every time.
Let me know if anyone have a VBA code to perform this. And also it would of great help if you have the code to check all the boxes or check only few boxes as desired under row labels in the pivot table.
So ultimately i need a VBA code that will:
1) Automatically hide any row that contains the words "(blank)"/ "N/A" under Row labels.
2) Automatically Check all the boxes under Row labels.
3) Automatically Check only few boxes under Row labels.
Thanks,
Ganesh
My source data has empty cells and #N/A. When I Pivot Table this data - the Pivot Table shows "(blank)" for the empty cells and #N/A under Row labels. I would not want to manually filter and uncheck (blank) and #N/A every time.
Let me know if anyone have a VBA code to perform this. And also it would of great help if you have the code to check all the boxes or check only few boxes as desired under row labels in the pivot table.
So ultimately i need a VBA code that will:
1) Automatically hide any row that contains the words "(blank)"/ "N/A" under Row labels.
2) Automatically Check all the boxes under Row labels.
3) Automatically Check only few boxes under Row labels.
Thanks,
Ganesh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
then try
For each pvtItm in ActiveSheet.PivotTables("P ivotTable1 ").PivotFi elds("MyPi votField") .PivotItem s
pvtItm.Visible = True
Next
For each pvtItm in ActiveSheet.PivotTables("P
pvtItm.Visible = True
Next
ASKER
Hi Rgonzo1971,
I used the above code but still unsuccessful in automatically selecting all the check boxes. I am relatively new to VBA coding, may be I am not placing the code at the right place.
It would of great help if you could help me editing the below mentioned code, in such a way that script will automatically selects all the check boxes under row labels in pivot table.
Below is the code I am using to fetch the data from one excel sheet to another and refreshing pivot table.
Sub PVDetails_DataFetch()
Dim filespec As Variant
Dim filename As String
MsgBox "Please select the pvdetails Week File"
filespec = Application.GetOpenFilenam e("Excel-f iles,*.xls x", _
1, "Select the PV Details File ", , False)
If TypeName(filespec) = "Boolean" Then Exit Sub
Workbooks.Open (filespec)
Sheets("PV DETAILS LAST WK-S").Select
Range("A3").Select
Range(Selection, Selection.End(xlToRight)). Select
Range(Selection, Selection.End(xlDown)).Sel ect
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.Copy
Windows("PV Template Automated Template.xlsm").Activate
Sheets("PV DETAILS LAST WK-S-RD").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Workbooks.Open (filespec)
Sheets("PV DETAILS THIS WK-S").Select
Range("A3").Select
Range(Selection, Selection.End(xlToRight)). Select
Range(Selection, Selection.End(xlDown)).Sel ect
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.Copy
Windows("PV Template Automated Template.xlsm").Activate
Sheets("PV DETAILS THIS WK-S -RD").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
Range("B5").Select
ActiveSheet.PivotTables("P ivotTable3 ").PivotCa che.Refres h
Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Sel ect
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.Copy
Sheets("PV DETAILS THIS WK-S").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
Range("C6").Select
Range(Selection, Selection.End(xlDown)).Sel ect
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.Copy
Sheets("PV DETAILS THIS WK-S").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("PV DETAILS LAST WK-S-PIV").Select
Range("B5").Select
ActiveSheet.PivotTables("P ivotTable1 ").PivotCa che.Refres h
Sheets("PV DETAILS LAST WK-S-PIV").Select
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Sel ect
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.Copy
Sheets("PV DETAILS LAST WK-S").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("PV DETAILS LAST WK-S-PIV").Select
Range("C6").Select
Range(Selection, Selection.End(xlDown)).Sel ect
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.Copy
Sheets("PV DETAILS LAST WK-S").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Macro").Select
End Sub
Please do let me know if you need any additional information!
Thanks,
G
I used the above code but still unsuccessful in automatically selecting all the check boxes. I am relatively new to VBA coding, may be I am not placing the code at the right place.
It would of great help if you could help me editing the below mentioned code, in such a way that script will automatically selects all the check boxes under row labels in pivot table.
Below is the code I am using to fetch the data from one excel sheet to another and refreshing pivot table.
Sub PVDetails_DataFetch()
Dim filespec As Variant
Dim filename As String
MsgBox "Please select the pvdetails Week File"
filespec = Application.GetOpenFilenam
1, "Select the PV Details File ", , False)
If TypeName(filespec) = "Boolean" Then Exit Sub
Workbooks.Open (filespec)
Sheets("PV DETAILS LAST WK-S").Select
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).
Range(Selection, Selection.End(xlDown)).Sel
Range(Selection, Selection.End(xlDown)).Sel
Selection.Copy
Windows("PV Template Automated Template.xlsm").Activate
Sheets("PV DETAILS LAST WK-S-RD").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Workbooks.Open (filespec)
Sheets("PV DETAILS THIS WK-S").Select
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).
Range(Selection, Selection.End(xlDown)).Sel
Range(Selection, Selection.End(xlDown)).Sel
Selection.Copy
Windows("PV Template Automated Template.xlsm").Activate
Sheets("PV DETAILS THIS WK-S -RD").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
Range("B5").Select
ActiveSheet.PivotTables("P
Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Sel
Range(Selection, Selection.End(xlDown)).Sel
Selection.Copy
Sheets("PV DETAILS THIS WK-S").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
Range("C6").Select
Range(Selection, Selection.End(xlDown)).Sel
Range(Selection, Selection.End(xlDown)).Sel
Selection.Copy
Sheets("PV DETAILS THIS WK-S").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("PV DETAILS LAST WK-S-PIV").Select
Range("B5").Select
ActiveSheet.PivotTables("P
Sheets("PV DETAILS LAST WK-S-PIV").Select
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Sel
Range(Selection, Selection.End(xlDown)).Sel
Selection.Copy
Sheets("PV DETAILS LAST WK-S").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("PV DETAILS LAST WK-S-PIV").Select
Range("C6").Select
Range(Selection, Selection.End(xlDown)).Sel
Range(Selection, Selection.End(xlDown)).Sel
Selection.Copy
Sheets("PV DETAILS LAST WK-S").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Macro").Select
End Sub
Please do let me know if you need any additional information!
Thanks,
G
ASKER
Thanks for your response!
Hope you had a great Christmas! Could not respond you immediately as i was on holiday.
The above code worked for me thanks a lot again. But it only for worked to deselect #N/A / Blank under the pivot table.
As i mentioned in the above question (Point number 2) i need code for
2) Automatically Check all the boxes under Row labels.
I am working on a VB scripted template where the data changes every week. The problem i am facing is every time data changes only few items will checked/selected under row labels in the pivot table. I have to manually check all the boxes every time. I have attached the word document for your reference.
It would of great help if you could help me with the code which automatically selects all the check boxes.
Will be awaiting your positive response!
Thanks,
G