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.

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(FieldName).PivotItems
If pvtItem.RecordCount <> 0 And pvtItem.Value <> "" Then

InputSheet.PivotTables(PivotName).PivotFields(FieldName).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 Before:=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.

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(FieldName).PivotItems
> If pvtItem.RecordCount <> 0 And pvtItem.Value <> "" Then
>
> InputSheet.PivotTables(PivotName).PivotFields(FieldName).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 Before:=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.

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(FieldName).PivotItems
> > If pvtItem.RecordCount <> 0 And pvtItem.Value <> "" Then
> >
> > InputSheet.PivotTables(PivotName).PivotFields(FieldName).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 Before:=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.