Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
Here's the situation. It's hard to imagine without seeing the whole sheet,
but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
This macro may provide a starting point for you:
Sub ShowItem() Dim SelItem As String, ItemFound As Boolean, pvtItm SelItem = ActiveSheet.Range("A1").Value ItemFound = False For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems pvtItm.Visible = True If pvtItm.Value = SelItem Then ItemFound = True Next pvtItm If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you paste them into the VBA editor in Excel. Hope this helps, Hutch "Becky" wrote: Here's the situation. It's hard to imagine without seeing the whole sheet, but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
Ok, I'm not that adept at using the editor. Can you explain more in detail
what I need to do, especially when you said "unwrap" below? "Tom Hutchins" wrote: This macro may provide a starting point for you: Sub ShowItem() Dim SelItem As String, ItemFound As Boolean, pvtItm SelItem = ActiveSheet.Range("A1").Value ItemFound = False For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems pvtItm.Visible = True If pvtItm.Value = SelItem Then ItemFound = True Next pvtItm If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you paste them into the VBA editor in Excel. Hope this helps, Hutch "Becky" wrote: Here's the situation. It's hard to imagine without seeing the whole sheet, but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
Sometimes a long line of code or a long comment wraps (splits into 2 lines)
when posted on the forum. That causes an error when the cose is posted into the VBA editor. Here is a slightly revised version which should avoid this problem (I have also added comments to the code): Public Sub ShowItem() 'Declare local varaiables Dim SelItem As String Dim ItemFound As Boolean, pvtItm 'Get the text entered by the user. SelItem = ActiveSheet.Range("A1").Value ItemFound = False 'Make every item in the pivottable visible. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems pvtItm.Visible = True 'If a pivottable item matches the user's text, set 'ItemFound to TRUE (was previously FALSE). If pvtItm.Value = SelItem Then ItemFound = True End If Next pvtItm 'If no item in the pivottable matches the user's text, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Hide every item in the pivottable that does not 'match the user's text. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub To use this macro: - right-click on any sheet tab in your workbook to open the VBA editor - From the menu bar, select Insert Module - Copy & paste the code above into the module - From the menu bar, select Debug Compile VBAProject - Save the workbook - You might add a command button on your worksheet near the user input cell. Right-click on the button and select 'View code'. In the Click event subroutine that appears, add the code 'Call ShowItem', as follows: Private Sub CommandButton1_Click() Call ShowItem End Sub Save, close, and re-open your workbook. The button should work. The macro above assumes the pivvottable is named PivotTable1. To see the name of your pivottable, click any cell in the pivottable. Then display the Pivot Table toolbar and select Pivot Table Table Options. The name of your table is displayed and can be changed in the box that appears. Likewise, the macro assumes the pivottable field you are trying to match is called 'Product'. Change it to the correct field name. Hope this helps, Hutch "Becky" wrote: Ok, I'm not that adept at using the editor. Can you explain more in detail what I need to do, especially when you said "unwrap" below? "Tom Hutchins" wrote: This macro may provide a starting point for you: Sub ShowItem() Dim SelItem As String, ItemFound As Boolean, pvtItm SelItem = ActiveSheet.Range("A1").Value ItemFound = False For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems pvtItm.Visible = True If pvtItm.Value = SelItem Then ItemFound = True Next pvtItm If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you paste them into the VBA editor in Excel. Hope this helps, Hutch "Becky" wrote: Here's the situation. It's hard to imagine without seeing the whole sheet, but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
Hey Hutch,
Thanks for all the info. My boss and I were working through it this morning, so we were able to get the macro to work. However, there's something else we'd like to tweak. When the macro runs, we want it to find the product number and stop at that point. It wants to run through every possible product number so it takes a minute or two for the macro to completely run. What can we add to the macro so that once it finds the product number we inserted into cell A1, it stops at that point and displays the product number & the corresponding data in the pivot table? "Tom Hutchins" wrote: Sometimes a long line of code or a long comment wraps (splits into 2 lines) when posted on the forum. That causes an error when the cose is posted into the VBA editor. Here is a slightly revised version which should avoid this problem (I have also added comments to the code): Public Sub ShowItem() 'Declare local varaiables Dim SelItem As String Dim ItemFound As Boolean, pvtItm 'Get the text entered by the user. SelItem = ActiveSheet.Range("A1").Value ItemFound = False 'Make every item in the pivottable visible. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems pvtItm.Visible = True 'If a pivottable item matches the user's text, set 'ItemFound to TRUE (was previously FALSE). If pvtItm.Value = SelItem Then ItemFound = True End If Next pvtItm 'If no item in the pivottable matches the user's text, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Hide every item in the pivottable that does not 'match the user's text. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub To use this macro: - right-click on any sheet tab in your workbook to open the VBA editor - From the menu bar, select Insert Module - Copy & paste the code above into the module - From the menu bar, select Debug Compile VBAProject - Save the workbook - You might add a command button on your worksheet near the user input cell. Right-click on the button and select 'View code'. In the Click event subroutine that appears, add the code 'Call ShowItem', as follows: Private Sub CommandButton1_Click() Call ShowItem End Sub Save, close, and re-open your workbook. The button should work. The macro above assumes the pivvottable is named PivotTable1. To see the name of your pivottable, click any cell in the pivottable. Then display the Pivot Table toolbar and select Pivot Table Table Options. The name of your table is displayed and can be changed in the box that appears. Likewise, the macro assumes the pivottable field you are trying to match is called 'Product'. Change it to the correct field name. Hope this helps, Hutch "Becky" wrote: Ok, I'm not that adept at using the editor. Can you explain more in detail what I need to do, especially when you said "unwrap" below? "Tom Hutchins" wrote: This macro may provide a starting point for you: Sub ShowItem() Dim SelItem As String, ItemFound As Boolean, pvtItm SelItem = ActiveSheet.Range("A1").Value ItemFound = False For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems pvtItm.Visible = True If pvtItm.Value = SelItem Then ItemFound = True Next pvtItm If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you paste them into the VBA editor in Excel. Hope this helps, Hutch "Becky" wrote: Here's the situation. It's hard to imagine without seeing the whole sheet, but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
We still need your help Hutch. Don't forget about us. :-)
"Becky" wrote: Hey Hutch, Thanks for all the info. My boss and I were working through it this morning, so we were able to get the macro to work. However, there's something else we'd like to tweak. When the macro runs, we want it to find the product number and stop at that point. It wants to run through every possible product number so it takes a minute or two for the macro to completely run. What can we add to the macro so that once it finds the product number we inserted into cell A1, it stops at that point and displays the product number & the corresponding data in the pivot table? "Tom Hutchins" wrote: Sometimes a long line of code or a long comment wraps (splits into 2 lines) when posted on the forum. That causes an error when the cose is posted into the VBA editor. Here is a slightly revised version which should avoid this problem (I have also added comments to the code): Public Sub ShowItem() 'Declare local varaiables Dim SelItem As String Dim ItemFound As Boolean, pvtItm 'Get the text entered by the user. SelItem = ActiveSheet.Range("A1").Value ItemFound = False 'Make every item in the pivottable visible. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems pvtItm.Visible = True 'If a pivottable item matches the user's text, set 'ItemFound to TRUE (was previously FALSE). If pvtItm.Value = SelItem Then ItemFound = True End If Next pvtItm 'If no item in the pivottable matches the user's text, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Hide every item in the pivottable that does not 'match the user's text. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub To use this macro: - right-click on any sheet tab in your workbook to open the VBA editor - From the menu bar, select Insert Module - Copy & paste the code above into the module - From the menu bar, select Debug Compile VBAProject - Save the workbook - You might add a command button on your worksheet near the user input cell. Right-click on the button and select 'View code'. In the Click event subroutine that appears, add the code 'Call ShowItem', as follows: Private Sub CommandButton1_Click() Call ShowItem End Sub Save, close, and re-open your workbook. The button should work. The macro above assumes the pivvottable is named PivotTable1. To see the name of your pivottable, click any cell in the pivottable. Then display the Pivot Table toolbar and select Pivot Table Table Options. The name of your table is displayed and can be changed in the box that appears. Likewise, the macro assumes the pivottable field you are trying to match is called 'Product'. Change it to the correct field name. Hope this helps, Hutch "Becky" wrote: Ok, I'm not that adept at using the editor. Can you explain more in detail what I need to do, especially when you said "unwrap" below? "Tom Hutchins" wrote: This macro may provide a starting point for you: Sub ShowItem() Dim SelItem As String, ItemFound As Boolean, pvtItm SelItem = ActiveSheet.Range("A1").Value ItemFound = False For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems pvtItm.Visible = True If pvtItm.Value = SelItem Then ItemFound = True Next pvtItm If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you paste them into the VBA editor in Excel. Hope this helps, Hutch "Becky" wrote: Here's the situation. It's hard to imagine without seeing the whole sheet, but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
In this section, add a line to exit the loop when the item is found:
If pvtItm.Value = SelItem Then ItemFound = True Exit For ''added this line End If Near the end, change the code to hide the visible items that don't match: If pvtItm.Value < SelItem Then pvtItm.Visible = False ''changed this End If Becky wrote: We still need your help Hutch. Don't forget about us. :-) "Becky" wrote: Hey Hutch, Thanks for all the info. My boss and I were working through it this morning, so we were able to get the macro to work. However, there's something else we'd like to tweak. When the macro runs, we want it to find the product number and stop at that point. It wants to run through every possible product number so it takes a minute or two for the macro to completely run. What can we add to the macro so that once it finds the product number we inserted into cell A1, it stops at that point and displays the product number & the corresponding data in the pivot table? "Tom Hutchins" wrote: Sometimes a long line of code or a long comment wraps (splits into 2 lines) when posted on the forum. That causes an error when the cose is posted into the VBA editor. Here is a slightly revised version which should avoid this problem (I have also added comments to the code): Public Sub ShowItem() 'Declare local varaiables Dim SelItem As String Dim ItemFound As Boolean, pvtItm 'Get the text entered by the user. SelItem = ActiveSheet.Range("A1").Value ItemFound = False 'Make every item in the pivottable visible. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems pvtItm.Visible = True 'If a pivottable item matches the user's text, set 'ItemFound to TRUE (was previously FALSE). If pvtItm.Value = SelItem Then ItemFound = True End If Next pvtItm 'If no item in the pivottable matches the user's text, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Hide every item in the pivottable that does not 'match the user's text. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub To use this macro: - right-click on any sheet tab in your workbook to open the VBA editor - From the menu bar, select Insert Module - Copy & paste the code above into the module - From the menu bar, select Debug Compile VBAProject - Save the workbook - You might add a command button on your worksheet near the user input cell. Right-click on the button and select 'View code'. In the Click event subroutine that appears, add the code 'Call ShowItem', as follows: Private Sub CommandButton1_Click() Call ShowItem End Sub Save, close, and re-open your workbook. The button should work. The macro above assumes the pivvottable is named PivotTable1. To see the name of your pivottable, click any cell in the pivottable. Then display the Pivot Table toolbar and select Pivot Table Table Options. The name of your table is displayed and can be changed in the box that appears. Likewise, the macro assumes the pivottable field you are trying to match is called 'Product'. Change it to the correct field name. Hope this helps, Hutch "Becky" wrote: Ok, I'm not that adept at using the editor. Can you explain more in detail what I need to do, especially when you said "unwrap" below? "Tom Hutchins" wrote: This macro may provide a starting point for you: Sub ShowItem() Dim SelItem As String, ItemFound As Boolean, pvtItm SelItem = ActiveSheet.Range("A1").Value ItemFound = False For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotF ields("Product").PivotItems pvtItm.Visible = True If pvtItm.Value = SelItem Then ItemFound = True Next pvtItm If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotF ields("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you paste them into the VBA editor in Excel. Hope this helps, Hutch "Becky" wrote: Here's the situation. It's hard to imagine without seeing the whole sheet, but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
Okay. It didn't work the way we wanted it, too. Let's say we have a list of
1,000 product numbers. When we put the product # in cell A1, the code makes the macro stop after the first cell, I believe, and returns the message "can't be found in pivot". We want it to go down through the list, find the product number, and display all associated with it. In a different way, is there a way for a macro to be created that would do this? If we entered the product # in cell A1, and then created a macro that would, in effect, select the pivot field button "product number", and then the macro would go down through the list of product #'s and select the number that is displayed in cell A1. "Debra Dalgleish" wrote: In this section, add a line to exit the loop when the item is found: If pvtItm.Value = SelItem Then ItemFound = True Exit For ''added this line End If Near the end, change the code to hide the visible items that don't match: If pvtItm.Value < SelItem Then pvtItm.Visible = False ''changed this End If Becky wrote: We still need your help Hutch. Don't forget about us. :-) "Becky" wrote: Hey Hutch, Thanks for all the info. My boss and I were working through it this morning, so we were able to get the macro to work. However, there's something else we'd like to tweak. When the macro runs, we want it to find the product number and stop at that point. It wants to run through every possible product number so it takes a minute or two for the macro to completely run. What can we add to the macro so that once it finds the product number we inserted into cell A1, it stops at that point and displays the product number & the corresponding data in the pivot table? "Tom Hutchins" wrote: Sometimes a long line of code or a long comment wraps (splits into 2 lines) when posted on the forum. That causes an error when the cose is posted into the VBA editor. Here is a slightly revised version which should avoid this problem (I have also added comments to the code): Public Sub ShowItem() 'Declare local varaiables Dim SelItem As String Dim ItemFound As Boolean, pvtItm 'Get the text entered by the user. SelItem = ActiveSheet.Range("A1").Value ItemFound = False 'Make every item in the pivottable visible. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems pvtItm.Visible = True 'If a pivottable item matches the user's text, set 'ItemFound to TRUE (was previously FALSE). If pvtItm.Value = SelItem Then ItemFound = True End If Next pvtItm 'If no item in the pivottable matches the user's text, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Hide every item in the pivottable that does not 'match the user's text. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub To use this macro: - right-click on any sheet tab in your workbook to open the VBA editor - From the menu bar, select Insert Module - Copy & paste the code above into the module - From the menu bar, select Debug Compile VBAProject - Save the workbook - You might add a command button on your worksheet near the user input cell. Right-click on the button and select 'View code'. In the Click event subroutine that appears, add the code 'Call ShowItem', as follows: Private Sub CommandButton1_Click() Call ShowItem End Sub Save, close, and re-open your workbook. The button should work. The macro above assumes the pivvottable is named PivotTable1. To see the name of your pivottable, click any cell in the pivottable. Then display the Pivot Table toolbar and select Pivot Table Table Options. The name of your table is displayed and can be changed in the box that appears. Likewise, the macro assumes the pivottable field you are trying to match is called 'Product'. Change it to the correct field name. Hope this helps, Hutch "Becky" wrote: Ok, I'm not that adept at using the editor. Can you explain more in detail what I need to do, especially when you said "unwrap" below? "Tom Hutchins" wrote: This macro may provide a starting point for you: Sub ShowItem() Dim SelItem As String, ItemFound As Boolean, pvtItm SelItem = ActiveSheet.Range("A1").Value ItemFound = False For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotF ields("Product").PivotItems pvtItm.Visible = True If pvtItm.Value = SelItem Then ItemFound = True Next pvtItm If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotF ields("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you paste them into the VBA editor in Excel. Hope this helps, Hutch "Becky" wrote: Here's the situation. It's hard to imagine without seeing the whole sheet, but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro (or equivalent) to display certain pivot table data
I didn't abandon you...I have been trying lots of possible alternative
solutions for your problem. You must have a huge pivottable if it takes that long to run. Two things make your request more challenging: 1) You can never hide all the records in a pivot table. At least one must be visible at all times; and 2) There is no single command to hide or unhide all the records at the same time. You have to loop through all the items. The last macro I posted looped through all the items twice. I have been searching for a way to loop through only once, making sure at least one record is always visible, and ultimately hiding all records but the desired one. Here is my solution: Public Sub ShowItem() 'Declare local variables Dim SelItem As String Dim ItemFound As Boolean, x As Long, pvtItm 'Get the text entered by the user. SelItem$ = ActiveSheet.Range("A1").Value ItemFound = False 'Make the first pivotitem visible Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems(1) pvtItm.Visible = True 'Hide every item in the pivottable that does not 'match the user's text. For x& = 2 To ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems.Count Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems(x&) If pvtItm = SelItem$ Then pvtItm.Visible = True ItemFound = True Else pvtItm.Visible = False End If Next x& 'Unless the first PivotItem matches the 'user's text, hide it. Set pvtItm = ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems(1) If pvtItm < SelItem$ Then If ItemFound = True Then pvtItm.Visible = False End If Else ItemFound = True End If 'If no item in the pivottable matches the user's text, '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 End Sub This makes the first item in the pivottable visible, then loops through the rest of the records once. Every record is hidden unless it matches the user's input. Finally, the first record is also hidden, unless it happens to match the user's input. Hope this helps, Hutch "Becky" wrote: Hey Hutch, Thanks for all the info. My boss and I were working through it this morning, so we were able to get the macro to work. However, there's something else we'd like to tweak. When the macro runs, we want it to find the product number and stop at that point. It wants to run through every possible product number so it takes a minute or two for the macro to completely run. What can we add to the macro so that once it finds the product number we inserted into cell A1, it stops at that point and displays the product number & the corresponding data in the pivot table? "Tom Hutchins" wrote: Sometimes a long line of code or a long comment wraps (splits into 2 lines) when posted on the forum. That causes an error when the cose is posted into the VBA editor. Here is a slightly revised version which should avoid this problem (I have also added comments to the code): Public Sub ShowItem() 'Declare local varaiables Dim SelItem As String Dim ItemFound As Boolean, pvtItm 'Get the text entered by the user. SelItem = ActiveSheet.Range("A1").Value ItemFound = False 'Make every item in the pivottable visible. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems pvtItm.Visible = True 'If a pivottable item matches the user's text, set 'ItemFound to TRUE (was previously FALSE). If pvtItm.Value = SelItem Then ItemFound = True End If Next pvtItm 'If no item in the pivottable matches the user's text, 'display an error message and quit. If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If 'Hide every item in the pivottable that does not 'match the user's text. For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable1"). _ PivotFields("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub To use this macro: - right-click on any sheet tab in your workbook to open the VBA editor - From the menu bar, select Insert Module - Copy & paste the code above into the module - From the menu bar, select Debug Compile VBAProject - Save the workbook - You might add a command button on your worksheet near the user input cell. Right-click on the button and select 'View code'. In the Click event subroutine that appears, add the code 'Call ShowItem', as follows: Private Sub CommandButton1_Click() Call ShowItem End Sub Save, close, and re-open your workbook. The button should work. The macro above assumes the pivvottable is named PivotTable1. To see the name of your pivottable, click any cell in the pivottable. Then display the Pivot Table toolbar and select Pivot Table Table Options. The name of your table is displayed and can be changed in the box that appears. Likewise, the macro assumes the pivottable field you are trying to match is called 'Product'. Change it to the correct field name. Hope this helps, Hutch "Becky" wrote: Ok, I'm not that adept at using the editor. Can you explain more in detail what I need to do, especially when you said "unwrap" below? "Tom Hutchins" wrote: This macro may provide a starting point for you: Sub ShowItem() Dim SelItem As String, ItemFound As Boolean, pvtItm SelItem = ActiveSheet.Range("A1").Value ItemFound = False For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems pvtItm.Visible = True If pvtItm.Value = SelItem Then ItemFound = True Next pvtItm If ItemFound = False Then MsgBox SelItem & " not found in pivot table" Exit Sub End If For Each pvtItm In ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems If pvtItm.Value = SelItem Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm End Sub The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you paste them into the VBA editor in Excel. Hope this helps, Hutch "Becky" wrote: Here's the situation. It's hard to imagine without seeing the whole sheet, but here goes. Assume someone enters a product number (e.g., 1438) into cell A1. Let's say I have a pivot table about 30 rows down that lists all the sales data YTD (with many different product numbers). I also have "ship to locations" for where this product is delivered and I want this displayed as well (along with respective VOL, $, etc.) I want to have a macro (or something equivalent) that will filter on the pivot data, so that only the product number listed in cell A1 is displayed in the pivot table below (with all the possible ship-to locations & corresponding data as well). It's the equivalent of clicking on the pivot drop-down menu and selecting the one product number. However, sales will change the product number depending on whatever number they want to input, so I can't have the macro specify a specific number, but just "any number". Hope this makes some sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Pivot Table to Display Filtered Data Only | Excel Discussion (Misc queries) | |||
How to DISPLAY all data values in Pivot Table...not count, sum, etc | Excel Discussion (Misc queries) | |||
pivot table data display in cell | Excel Discussion (Misc queries) | |||
Pivot Table: Display data | Setting up and Configuration of Excel | |||
Text data appears as 0 in my Pivot table - how do I display as tex | Excel Worksheet Functions |