| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Pivot Table
While in the pivot table and looking at the Fields I have a field called Rel.
This contains anywhere from 50 to 100 different releases such as: 4.0 - 4.5 - 4.05.50 etc. I know while in the drop down box where it ask you to show all or select the ones you want I can just start the number and it will automatically go to it. However these are not sorted (not my data) and I have to manuualy search for the ones that are like 4.05.50. I would like a macro to run inside this drop box and put a check mark against the string of releases. Can do?? Any other suggestions. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Pivot Table
Try the following macro. Paste it into a VBA module in your workbook. This
macro assumes your pivot table is named "PivotTable1". Click any cell on the sheet where the pivot table is, then run the ShowItem macro (Tools Macro Macros ShowItem RUn). Option Explicit Public Sub ShowItem() 'Declare local variables Dim ItemFound As Boolean, x As Long, pvtItm Const WhichFld = "Rel" Const SelItem = "4.05.50" ItemFound = False 'Make the first pivotitem visible Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(1) pvtItm.Visible = True 'Hide every item in the pivottable that does not 'match SelItem. For x& = 2 To ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems.Count Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(x&) If Len(pvtItm) = Len(SelItem) Then If Left(pvtItm, Len(SelItem)) = SelItem Then pvtItm.Visible = True ItemFound = True Else pvtItm.Visible = False End If Else pvtItm.Visible = False End If Next x& 'Unless the first PivotItem matches SelItem, hide it. Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(1) If Len(pvtItm) = Len(SelItem) Then If Left(pvtItm, Len(SelItem)) = SelItem Then ItemFound = True Else pvtItm.Visible = False End If Else pvtItm.Visible = False End If 'If no item in the pivottable matches SelItem, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Free object variables Set pvtItm = Nothing Exit Sub SIerr: MsgBox Err.Description End Sub Hope this helps, Hutch "Beep Beep" wrote: While in the pivot table and looking at the Fields I have a field called Rel. This contains anywhere from 50 to 100 different releases such as: 4.0 - 4.5 - 4.05.50 etc. I know while in the drop down box where it ask you to show all or select the ones you want I can just start the number and it will automatically go to it. However these are not sorted (not my data) and I have to manuualy search for the ones that are like 4.05.50. I would like a macro to run inside this drop box and put a check mark against the string of releases. Can do?? Any other suggestions. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Pivot Table
Hey Tom
The macro stopped at pvtItm.Visible = True "Tom Hutchins" wrote: Try the following macro. Paste it into a VBA module in your workbook. This macro assumes your pivot table is named "PivotTable1". Click any cell on the sheet where the pivot table is, then run the ShowItem macro (Tools Macro Macros ShowItem RUn). Option Explicit Public Sub ShowItem() 'Declare local variables Dim ItemFound As Boolean, x As Long, pvtItm Const WhichFld = "Rel" Const SelItem = "4.05.50" ItemFound = False 'Make the first pivotitem visible Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(1) pvtItm.Visible = True 'Hide every item in the pivottable that does not 'match SelItem. For x& = 2 To ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems.Count Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(x&) If Len(pvtItm) = Len(SelItem) Then If Left(pvtItm, Len(SelItem)) = SelItem Then pvtItm.Visible = True ItemFound = True Else pvtItm.Visible = False End If Else pvtItm.Visible = False End If Next x& 'Unless the first PivotItem matches SelItem, hide it. Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(1) If Len(pvtItm) = Len(SelItem) Then If Left(pvtItm, Len(SelItem)) = SelItem Then ItemFound = True Else pvtItm.Visible = False End If Else pvtItm.Visible = False End If 'If no item in the pivottable matches SelItem, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Free object variables Set pvtItm = Nothing Exit Sub SIerr: MsgBox Err.Description End Sub Hope this helps, Hutch "Beep Beep" wrote: While in the pivot table and looking at the Fields I have a field called Rel. This contains anywhere from 50 to 100 different releases such as: 4.0 - 4.5 - 4.05.50 etc. I know while in the drop down box where it ask you to show all or select the ones you want I can just start the number and it will automatically go to it. However these are not sorted (not my data) and I have to manuualy search for the ones that are like 4.05.50. I would like a macro to run inside this drop box and put a check mark against the string of releases. Can do?? Any other suggestions. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Pivot Table
I'm not sure why that would happen. It works fine with two dummy pivot tables
I created based on your description. Is Rel the exact name of the field? What kind of field is it: a row field (vertical along the left side), a column field (horizontal along the top), a page field (separate & above the rest of the table), or a data field? Hutch "Beep Beep" wrote: Hey Tom The macro stopped at pvtItm.Visible = True "Tom Hutchins" wrote: Try the following macro. Paste it into a VBA module in your workbook. This macro assumes your pivot table is named "PivotTable1". Click any cell on the sheet where the pivot table is, then run the ShowItem macro (Tools Macro Macros ShowItem RUn). Option Explicit Public Sub ShowItem() 'Declare local variables Dim ItemFound As Boolean, x As Long, pvtItm Const WhichFld = "Rel" Const SelItem = "4.05.50" ItemFound = False 'Make the first pivotitem visible Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(1) pvtItm.Visible = True 'Hide every item in the pivottable that does not 'match SelItem. For x& = 2 To ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems.Count Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(x&) If Len(pvtItm) = Len(SelItem) Then If Left(pvtItm, Len(SelItem)) = SelItem Then pvtItm.Visible = True ItemFound = True Else pvtItm.Visible = False End If Else pvtItm.Visible = False End If Next x& 'Unless the first PivotItem matches SelItem, hide it. Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(1) If Len(pvtItm) = Len(SelItem) Then If Left(pvtItm, Len(SelItem)) = SelItem Then ItemFound = True Else pvtItm.Visible = False End If Else pvtItm.Visible = False End If 'If no item in the pivottable matches SelItem, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Free object variables Set pvtItm = Nothing Exit Sub SIerr: MsgBox Err.Description End Sub Hope this helps, Hutch "Beep Beep" wrote: While in the pivot table and looking at the Fields I have a field called Rel. This contains anywhere from 50 to 100 different releases such as: 4.0 - 4.5 - 4.05.50 etc. I know while in the drop down box where it ask you to show all or select the ones you want I can just start the number and it will automatically go to it. However these are not sorted (not my data) and I have to manuualy search for the ones that are like 4.05.50. I would like a macro to run inside this drop box and put a check mark against the string of releases. Can do?? Any other suggestions. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Pivot Table
If the field is set for automatic sort, you'll get an error. You can add
some code to set the sort to manual, e.g.: With ActiveSheet.PivotTables(1).PivotFields(WhichFld$) .AutoSort xlManual, .Name End With pvtItm.Visible = True Beep Beep wrote: Hey Tom The macro stopped at pvtItm.Visible = True "Tom Hutchins" wrote: Try the following macro. Paste it into a VBA module in your workbook. This macro assumes your pivot table is named "PivotTable1". Click any cell on the sheet where the pivot table is, then run the ShowItem macro (Tools Macro Macros ShowItem RUn). Option Explicit Public Sub ShowItem() 'Declare local variables Dim ItemFound As Boolean, x As Long, pvtItm Const WhichFld = "Rel" Const SelItem = "4.05.50" ItemFound = False 'Make the first pivotitem visible Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(1) pvtItm.Visible = True 'Hide every item in the pivottable that does not 'match SelItem. For x& = 2 To ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems.Count Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(x&) If Len(pvtItm) = Len(SelItem) Then If Left(pvtItm, Len(SelItem)) = SelItem Then pvtItm.Visible = True ItemFound = True Else pvtItm.Visible = False End If Else pvtItm.Visible = False End If Next x& 'Unless the first PivotItem matches SelItem, hide it. Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields(WhichFld$).PivotItems(1) If Len(pvtItm) = Len(SelItem) Then If Left(pvtItm, Len(SelItem)) = SelItem Then ItemFound = True Else pvtItm.Visible = False End If Else pvtItm.Visible = False End If 'If no item in the pivottable matches SelItem, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Free object variables Set pvtItm = Nothing Exit Sub SIerr: MsgBox Err.Description End Sub Hope this helps, Hutch "Beep Beep" wrote: While in the pivot table and looking at the Fields I have a field called Rel. This contains anywhere from 50 to 100 different releases such as: 4.0 - 4.5 - 4.05.50 etc. I know while in the drop down box where it ask you to show all or select the ones you want I can just start the number and it will automatically go to it. However these are not sorted (not my data) and I have to manuualy search for the ones that are like 4.05.50. I would like a macro to run inside this drop box and put a check mark against the string of releases. Can do?? Any other suggestions. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code for a Macro that allows a Pivot table to be repeatedly run (fora dynamic rows in a table, but static field names) | Excel Programming | |||
| Pivot Table Macro | Excel Discussion (Misc queries) | |||
| Pivot Table Macro Help | Excel Programming | |||
| Macro and Pivot Table | Excel Programming | |||
| Steps from Macro recorder for Pivot table will not run as a Macro | Excel Programming | |||
Linear Mode
