Pivot Table Macro
 

I have created a PT that seperates a bunch of information out of a master
log. In the "Page Field" of the table I have the whole list filtered down by
a specific persons name. There is roughtly 30 names possible in this field
and I want to be able to run a macro that selects the top name in the range
(this will filter my PT) copy all the active information on the sheet
open a new workbook paste the values I just copied and save. Then I want to
go back to my original PT and open up the "Page Field" again and select the
next active name and repeat until I have gone thru all active names.

Can this be done?
Any help is Greatly Apprechiated.
Peter

Pivot Table Macro
 

Can you do this for me? Perform the function you want done for the first
name and record that as a macro. Then come back here and post what you have
and we can help clean it up.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

I have created a PT that seperates a bunch of information out of a master
log. In the "Page Field" of the table I have the whole list filtered down by
a specific persons name. There is roughtly 30 names possible in this field
and I want to be able to run a macro that selects the top name in the range
(this will filter my PT) copy all the active information on the sheet
open a new workbook paste the values I just copied and save. Then I want to
go back to my original PT and open up the "Page Field" again and select the
next active name and repeat until I have gone thru all active names.

Can this be done?
Any help is Greatly Apprechiated.
Peter

Pivot Table Macro
 

this is what came out of the recorder after selecting the top two names

Sub Filter_Rep()

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"01 - Power Reps"
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Cells.Select
Windows("Quote log 2007.xls").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"02-AC & DC Power Tech"
Cells.Select
ActiveSheet.Paste
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks
Peter


"Barb Reinhardt" wrote:

Can you do this for me? Perform the function you want done for the first
name and record that as a macro. Then come back here and post what you have
and we can help clean it up.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

I have created a PT that seperates a bunch of information out of a master
log. In the "Page Field" of the table I have the whole list filtered down by
a specific persons name. There is roughtly 30 names possible in this field
and I want to be able to run a macro that selects the top name in the range
(this will filter my PT) copy all the active information on the sheet
open a new workbook paste the values I just copied and save. Then I want to
go back to my original PT and open up the "Page Field" again and select the
next active name and repeat until I have gone thru all active names.

Can this be done?
Any help is Greatly Apprechiated.
Peter

Pivot Table Macro
 

I can't guarantee that this will work, as I don't have your exact pivot
table, but try it on a copy of your workbook. You need to run it from the
sheet with the pivot table.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

this is what came out of the recorder after selecting the top two names

Sub Filter_Rep()

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"01 - Power Reps"
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Cells.Select
Windows("Quote log 2007.xls").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"02-AC & DC Power Tech"
Cells.Select
ActiveSheet.Paste
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks
Peter


"Barb Reinhardt" wrote:

Can you do this for me? Perform the function you want done for the first
name and record that as a macro. Then come back here and post what you have
and we can help clean it up.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

I have created a PT that seperates a bunch of information out of a master
log. In the "Page Field" of the table I have the whole list filtered down by
a specific persons name. There is roughtly 30 names possible in this field
and I want to be able to run a macro that selects the top name in the range
(this will filter my PT) copy all the active information on the sheet
open a new workbook paste the values I just copied and save. Then I want to
go back to my original PT and open up the "Page Field" again and select the
next active name and repeat until I have gone thru all active names.

Can this be done?
Any help is Greatly Apprechiated.
Peter

Pivot Table Macro
 

Oops, try this

Sub Filter_Rep()
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myPivotItem As PivotItem

Set aWS = ActiveSheet

For Each myPivotField In aWS.PivotTables("PivotTable1").PivotFields
Debug.Print myPivotField.Name
If myPivotField.Name = "Rep." Then
For Each myPivotItem In myPivotField.PivotItems
Debug.Print myPivotItem.Value
If Not myPivotItem.Value = "(blank)" Then
myPivotField.CurrentPage = myPivotItem.Value
Set myWB = Workbooks.Add
Set myWS = myWB.Worksheets(1)
myWS.Range(aWS.UsedRange.Address) = aWS.UsedRange
Debug.Print aWS.UsedRange.Address
aWS.UsedRange.Copy
myWS.PasteSpecial
End If
Next myPivotItem
End If
Next myPivotField
End Sub
--
HTH,
Barb Reinhardt



"Barb Reinhardt" wrote:

I can't guarantee that this will work, as I don't have your exact pivot
table, but try it on a copy of your workbook. You need to run it from the
sheet with the pivot table.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

this is what came out of the recorder after selecting the top two names

Sub Filter_Rep()

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"01 - Power Reps"
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Cells.Select
Windows("Quote log 2007.xls").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"02-AC & DC Power Tech"
Cells.Select
ActiveSheet.Paste
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks
Peter


"Barb Reinhardt" wrote:

Can you do this for me? Perform the function you want done for the first
name and record that as a macro. Then come back here and post what you have
and we can help clean it up.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

I have created a PT that seperates a bunch of information out of a master
log. In the "Page Field" of the table I have the whole list filtered down by
a specific persons name. There is roughtly 30 names possible in this field
and I want to be able to run a macro that selects the top name in the range
(this will filter my PT) copy all the active information on the sheet
open a new workbook paste the values I just copied and save. Then I want to
go back to my original PT and open up the "Page Field" again and select the
next active name and repeat until I have gone thru all active names.

Can this be done?
Any help is Greatly Apprechiated.
Peter

Pivot Table Macro
 

Try what?

"Barb Reinhardt" wrote:

I can't guarantee that this will work, as I don't have your exact pivot
table, but try it on a copy of your workbook. You need to run it from the
sheet with the pivot table.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

this is what came out of the recorder after selecting the top two names

Sub Filter_Rep()

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"01 - Power Reps"
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Cells.Select
Windows("Quote log 2007.xls").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"02-AC & DC Power Tech"
Cells.Select
ActiveSheet.Paste
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks
Peter


"Barb Reinhardt" wrote:

Can you do this for me? Perform the function you want done for the first
name and record that as a macro. Then come back here and post what you have
and we can help clean it up.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

I have created a PT that seperates a bunch of information out of a master
log. In the "Page Field" of the table I have the whole list filtered down by
a specific persons name. There is roughtly 30 names possible in this field
and I want to be able to run a macro that selects the top name in the range
(this will filter my PT) copy all the active information on the sheet
open a new workbook paste the values I just copied and save. Then I want to
go back to my original PT and open up the "Page Field" again and select the
next active name and repeat until I have gone thru all active names.

Can this be done?
Any help is Greatly Apprechiated.
Peter

Pivot Table Macro
 

Barb, Happy new year.

I have added this code to my workbook and tried it. I get a run time error
1004 (Unable to set the _Default property of the Pivotitem class) when I
debug the line of code highlighted is "myPivotField.CurrentPage =
myPivotItem.Value"

thanks for your help to this point.
Peter

"Barb Reinhardt" wrote:

Oops, try this

Sub Filter_Rep()
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myPivotItem As PivotItem

Set aWS = ActiveSheet

For Each myPivotField In aWS.PivotTables("PivotTable1").PivotFields
Debug.Print myPivotField.Name
If myPivotField.Name = "Rep." Then
For Each myPivotItem In myPivotField.PivotItems
Debug.Print myPivotItem.Value
If Not myPivotItem.Value = "(blank)" Then
myPivotField.CurrentPage = myPivotItem.Value
Set myWB = Workbooks.Add
Set myWS = myWB.Worksheets(1)
myWS.Range(aWS.UsedRange.Address) = aWS.UsedRange
Debug.Print aWS.UsedRange.Address
aWS.UsedRange.Copy
myWS.PasteSpecial
End If
Next myPivotItem
End If
Next myPivotField
End Sub
--
HTH,
Barb Reinhardt



"Barb Reinhardt" wrote:

I can't guarantee that this will work, as I don't have your exact pivot
table, but try it on a copy of your workbook. You need to run it from the
sheet with the pivot table.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

this is what came out of the recorder after selecting the top two names

Sub Filter_Rep()

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"01 - Power Reps"
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Cells.Select
Windows("Quote log 2007.xls").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rep.").CurrentPage = _
"02-AC & DC Power Tech"
Cells.Select
ActiveSheet.Paste
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks
Peter


"Barb Reinhardt" wrote:

Can you do this for me? Perform the function you want done for the first
name and record that as a macro. Then come back here and post what you have
and we can help clean it up.
--
HTH,
Barb Reinhardt



"Looping through" wrote:

I have created a PT that seperates a bunch of information out of a master
log. In the "Page Field" of the table I have the whole list filtered down by
a specific persons name. There is roughtly 30 names possible in this field
and I want to be able to run a macro that selects the top name in the range
(this will filter my PT) copy all the active information on the sheet
open a new workbook paste the values I just copied and save. Then I want to
go back to my original PT and open up the "Page Field" again and select the
next active name and repeat until I have gone thru all active names.

Can this be done?
Any help is Greatly Apprechiated.
Peter