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.