Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Pivot Table drop down menu
Explanation of current process:
Produce 11 groups of reports each month. I use Access to generate the data and export to an XLS file for each group. The data is in the same column layout but varies in size for each group. I have the same spread sheet layout in 11 spreadsheets and have used an offset formula to define the data area on worksheet named "DATA". I take the access export and copy the data paste it on the worksheet "DATA" in the apropriate spreadsheet. In each spreadsheet there are 5 pivottables and two other worksheets. The pivot tables data is based on the named range so I simply go to each tab and update them and that works just fine. The other two sheets are using "sumproduct" so I do a search and replace for the last row value and that fixes them just fine. (the report data can be longer or shorter). All sheets are filtered by name so I print one copy based on all names and then cycle through each pivot table selecting the next name on pivot table one and the selecting the same name on all other pivot tables (due to the fact the pivot data is not sorted the same the names are not in the same order on each pivot table). To select the names for the other two sheets I use advance filter to grab all the unique names from the "DATA" worksheet and copy that to a section on the sheet. I then create a drop down list based on that section and select the name from it. I then print 4 copies of the report based on that individual name. ( I have figured out that I can use a formula in the drop down list cell that equals the current pivot table selection for "Name" on pivot table one. That sped up the process some). What I would like to learn how to do is write a macro that would just select the name in the pivot table and print then cycle to the next name and print etc.. My groups are static so ideally I would ideally like to work this where I could do this all in one fatal swoop. I know this is very long and complicated but even a few nudges in the proper direction would be helpful. I need to streamline this process as much as possible. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Pivot Table drop down menu
Here is some code that I use to traverse through the names in a drop down
menu. This sub creates a new workbook for each seperate item in the drop down... There is a bunch of specific crap in here you will have to strip out but the basic form is there.... HTH Public Sub PrintAllManagers(ByVal InputSheet As Worksheet, ByVal PivotName As String, _ ByVal FieldName As String) Dim pvtItem As PivotItem Dim strCurrentSheetName As String Dim wbkCurrent As Workbook Dim wbkDestination As Workbook Dim blnBranchSheet As Boolean strCurrentSheetName = InputSheet.Name If strCurrentSheetName = shtPartBBranchManager.Name Then blnBranchSheet = True Else blnBranchSheet = False End If Set wbkCurrent = ActiveWorkbook Set wbkDestination = Workbooks.Add For Each pvtItem In InputSheet.PivotTables(PivotName).PivotFields(Fiel dName).PivotItems If pvtItem.RecordCount < 0 And pvtItem.Value < "" Then InputSheet.PivotTables(PivotName).PivotFields(Fiel dName).CurrentPage = pvtItem.Name Call modFormatSheet.FormatSheetToPrint(InputSheet) If blnBranchSheet And shtPartBBranchManager.Range("A12").Value < Empty Then If Len(shtPartBBranchManager.Range("A12").Value) 7 Then InputSheet.Name = Left(shtPartBBranchManager.Range("A12").Value, 4) Else InputSheet.Name = Right(shtPartBBranchManager.Range("A12").Value, 4) InputSheet.Columns("A:A").ColumnWidth = 18.5 End If Else InputSheet.Name = pvtItem.Value End If InputSheet.Copy Befo=wbkDestination.Sheets("Sheet1") wbkDestination.ActiveSheet.Protect Password:=m_cFPAPassword End If Next Application.DisplayAlerts = False wbkDestination.Sheets("Sheet1").Delete wbkDestination.Sheets("Sheet2").Delete wbkDestination.Sheets("Sheet3").Delete Application.DisplayAlerts = True wbkCurrent.Activate InputSheet.Name = strCurrentSheetName Set wbkCurrent = Nothing Set wbkDestination = Nothing End Sub "Michael" wrote: Explanation of current process: Produce 11 groups of reports each month. I use Access to generate the data and export to an XLS file for each group. The data is in the same column layout but varies in size for each group. I have the same spread sheet layout in 11 spreadsheets and have used an offset formula to define the data area on worksheet named "DATA". I take the access export and copy the data paste it on the worksheet "DATA" in the apropriate spreadsheet. In each spreadsheet there are 5 pivottables and two other worksheets. The pivot tables data is based on the named range so I simply go to each tab and update them and that works just fine. The other two sheets are using "sumproduct" so I do a search and replace for the last row value and that fixes them just fine. (the report data can be longer or shorter). All sheets are filtered by name so I print one copy based on all names and then cycle through each pivot table selecting the next name on pivot table one and the selecting the same name on all other pivot tables (due to the fact the pivot data is not sorted the same the names are not in the same order on each pivot table). To select the names for the other two sheets I use advance filter to grab all the unique names from the "DATA" worksheet and copy that to a section on the sheet. I then create a drop down list based on that section and select the name from it. I then print 4 copies of the report based on that individual name. ( I have figured out that I can use a formula in the drop down list cell that equals the current pivot table selection for "Name" on pivot table one. That sped up the process some). What I would like to learn how to do is write a macro that would just select the name in the pivot table and print then cycle to the next name and print etc.. My groups are static so ideally I would ideally like to work this where I could do this all in one fatal swoop. I know this is very long and complicated but even a few nudges in the proper direction would be helpful. I need to streamline this process as much as possible. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Pivot Table drop down menu
Sorry One workbook with a whole bunch of tabs, each tab represneting a
different itme form the drop down... "Jim Thomlinson" wrote: Here is some code that I use to traverse through the names in a drop down menu. This sub creates a new workbook for each seperate item in the drop down... There is a bunch of specific crap in here you will have to strip out but the basic form is there.... HTH Public Sub PrintAllManagers(ByVal InputSheet As Worksheet, ByVal PivotName As String, _ ByVal FieldName As String) Dim pvtItem As PivotItem Dim strCurrentSheetName As String Dim wbkCurrent As Workbook Dim wbkDestination As Workbook Dim blnBranchSheet As Boolean strCurrentSheetName = InputSheet.Name If strCurrentSheetName = shtPartBBranchManager.Name Then blnBranchSheet = True Else blnBranchSheet = False End If Set wbkCurrent = ActiveWorkbook Set wbkDestination = Workbooks.Add For Each pvtItem In InputSheet.PivotTables(PivotName).PivotFields(Fiel dName).PivotItems If pvtItem.RecordCount < 0 And pvtItem.Value < "" Then InputSheet.PivotTables(PivotName).PivotFields(Fiel dName).CurrentPage = pvtItem.Name Call modFormatSheet.FormatSheetToPrint(InputSheet) If blnBranchSheet And shtPartBBranchManager.Range("A12").Value < Empty Then If Len(shtPartBBranchManager.Range("A12").Value) 7 Then InputSheet.Name = Left(shtPartBBranchManager.Range("A12").Value, 4) Else InputSheet.Name = Right(shtPartBBranchManager.Range("A12").Value, 4) InputSheet.Columns("A:A").ColumnWidth = 18.5 End If Else InputSheet.Name = pvtItem.Value End If InputSheet.Copy Befo=wbkDestination.Sheets("Sheet1") wbkDestination.ActiveSheet.Protect Password:=m_cFPAPassword End If Next Application.DisplayAlerts = False wbkDestination.Sheets("Sheet1").Delete wbkDestination.Sheets("Sheet2").Delete wbkDestination.Sheets("Sheet3").Delete Application.DisplayAlerts = True wbkCurrent.Activate InputSheet.Name = strCurrentSheetName Set wbkCurrent = Nothing Set wbkDestination = Nothing End Sub "Michael" wrote: Explanation of current process: Produce 11 groups of reports each month. I use Access to generate the data and export to an XLS file for each group. The data is in the same column layout but varies in size for each group. I have the same spread sheet layout in 11 spreadsheets and have used an offset formula to define the data area on worksheet named "DATA". I take the access export and copy the data paste it on the worksheet "DATA" in the apropriate spreadsheet. In each spreadsheet there are 5 pivottables and two other worksheets. The pivot tables data is based on the named range so I simply go to each tab and update them and that works just fine. The other two sheets are using "sumproduct" so I do a search and replace for the last row value and that fixes them just fine. (the report data can be longer or shorter). All sheets are filtered by name so I print one copy based on all names and then cycle through each pivot table selecting the next name on pivot table one and the selecting the same name on all other pivot tables (due to the fact the pivot data is not sorted the same the names are not in the same order on each pivot table). To select the names for the other two sheets I use advance filter to grab all the unique names from the "DATA" worksheet and copy that to a section on the sheet. I then create a drop down list based on that section and select the name from it. I then print 4 copies of the report based on that individual name. ( I have figured out that I can use a formula in the drop down list cell that equals the current pivot table selection for "Name" on pivot table one. That sped up the process some). What I would like to learn how to do is write a macro that would just select the name in the pivot table and print then cycle to the next name and print etc.. My groups are static so ideally I would ideally like to work this where I could do this all in one fatal swoop. I know this is very long and complicated but even a few nudges in the proper direction would be helpful. I need to streamline this process as much as possible. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through Pivot Table drop down menu
Thanks for the quick response. I am currently reading through the code to
see if it "nudges" me in the right direction. "Jim Thomlinson" wrote: Sorry One workbook with a whole bunch of tabs, each tab represneting a different itme form the drop down... "Jim Thomlinson" wrote: Here is some code that I use to traverse through the names in a drop down menu. This sub creates a new workbook for each seperate item in the drop down... There is a bunch of specific crap in here you will have to strip out but the basic form is there.... HTH Public Sub PrintAllManagers(ByVal InputSheet As Worksheet, ByVal PivotName As String, _ ByVal FieldName As String) Dim pvtItem As PivotItem Dim strCurrentSheetName As String Dim wbkCurrent As Workbook Dim wbkDestination As Workbook Dim blnBranchSheet As Boolean strCurrentSheetName = InputSheet.Name If strCurrentSheetName = shtPartBBranchManager.Name Then blnBranchSheet = True Else blnBranchSheet = False End If Set wbkCurrent = ActiveWorkbook Set wbkDestination = Workbooks.Add For Each pvtItem In InputSheet.PivotTables(PivotName).PivotFields(Fiel dName).PivotItems If pvtItem.RecordCount < 0 And pvtItem.Value < "" Then InputSheet.PivotTables(PivotName).PivotFields(Fiel dName).CurrentPage = pvtItem.Name Call modFormatSheet.FormatSheetToPrint(InputSheet) If blnBranchSheet And shtPartBBranchManager.Range("A12").Value < Empty Then If Len(shtPartBBranchManager.Range("A12").Value) 7 Then InputSheet.Name = Left(shtPartBBranchManager.Range("A12").Value, 4) Else InputSheet.Name = Right(shtPartBBranchManager.Range("A12").Value, 4) InputSheet.Columns("A:A").ColumnWidth = 18.5 End If Else InputSheet.Name = pvtItem.Value End If InputSheet.Copy Befo=wbkDestination.Sheets("Sheet1") wbkDestination.ActiveSheet.Protect Password:=m_cFPAPassword End If Next Application.DisplayAlerts = False wbkDestination.Sheets("Sheet1").Delete wbkDestination.Sheets("Sheet2").Delete wbkDestination.Sheets("Sheet3").Delete Application.DisplayAlerts = True wbkCurrent.Activate InputSheet.Name = strCurrentSheetName Set wbkCurrent = Nothing Set wbkDestination = Nothing End Sub "Michael" wrote: Explanation of current process: Produce 11 groups of reports each month. I use Access to generate the data and export to an XLS file for each group. The data is in the same column layout but varies in size for each group. I have the same spread sheet layout in 11 spreadsheets and have used an offset formula to define the data area on worksheet named "DATA". I take the access export and copy the data paste it on the worksheet "DATA" in the apropriate spreadsheet. In each spreadsheet there are 5 pivottables and two other worksheets. The pivot tables data is based on the named range so I simply go to each tab and update them and that works just fine. The other two sheets are using "sumproduct" so I do a search and replace for the last row value and that fixes them just fine. (the report data can be longer or shorter). All sheets are filtered by name so I print one copy based on all names and then cycle through each pivot table selecting the next name on pivot table one and the selecting the same name on all other pivot tables (due to the fact the pivot data is not sorted the same the names are not in the same order on each pivot table). To select the names for the other two sheets I use advance filter to grab all the unique names from the "DATA" worksheet and copy that to a section on the sheet. I then create a drop down list based on that section and select the name from it. I then print 4 copies of the report based on that individual name. ( I have figured out that I can use a formula in the drop down list cell that equals the current pivot table selection for "Name" on pivot table one. That sped up the process some). What I would like to learn how to do is write a macro that would just select the name in the pivot table and print then cycle to the next name and print etc.. My groups are static so ideally I would ideally like to work this where I could do this all in one fatal swoop. I know this is very long and complicated but even a few nudges in the proper direction would be helpful. I need to streamline this process as much as possible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table DATA area drop down menu items disapper after switchof | Excel Discussion (Misc queries) | |||
how to hide "All" in drop-down menu of my pivot table | Excel Discussion (Misc queries) | |||
Deleting an otpion on the drop down menu on a pivot table | Excel Discussion (Misc queries) | |||
For Each Loop with Pivot Table | Excel Discussion (Misc queries) | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel |