I am struggling trying to create a pivot table from filtered data. I am
enclosing the sub but am scared to death to do it because I now it is not
pristine code. I believe the problem is that I successfully filter the data
but then I select all of the rows for the pivot table rather than just the
filtered rows. Any help is appreciated.

Sub Create_2nd_Pivot_Table()
'
'
'Filter for Month selected
xMonth = Sheets("Stats").Range("E37").Value
yMonth = xMonth + 1
xYear = Sheets("Stats").Range("D37").Value
If xMonth = 12 Then
yYear = xYear + 1
Else
yYear = xYear
End If
BegMonth = xMonth & "/1/" & xYear
EndMonth = yMonth & "/1/" & yYear


Sheets("Log").Select
'Selection.AutoFilter Field:=7, Criteria1:="=9/1/2008",
Operator:=xlAnd, _
Criteria2:="<10/1/2008"
Selection.AutoFilter Field:=7, Criteria1:="=" & BegMonth,
Operator:=xlAnd, _
Criteria2:="<" & EndMonth

' Clear old Pivot table area on Work Col a-l
Sheets("Work").Visible = True
Sheets("Work").Select
Range("A4:l500").Clear
Range("A4").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Log!R5C1:R500C10").CreatePivotTable TableDestination:= _
"Work!R4C1", TableName:="PivotTable5", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable5").ColumnGrand = False
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Circuit")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Trainer")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable5").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("Course Hours"), "Sum of Course
Hours", _
xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
Sheets("Work").Visible = False
Application.ScreenUpdating = False
Sheets("Stats").Activate
Range("F37").Select
End Sub

It doesn't matter if the source table is filtered or not -- the pivot
table is created from the entire table, including any hidden rows.

You could use an Advanced Filter to extract the results to a different
area of the workbook, and create the pivot table from the extracted data.

Bigfoot17 wrote:
I am struggling trying to create a pivot table from filtered data. I am
enclosing the sub but am scared to death to do it because I now it is not
pristine code. I believe the problem is that I successfully filter the data
but then I select all of the rows for the pivot table rather than just the
filtered rows. Any help is appreciated.

Sub Create_2nd_Pivot_Table()
'
'
'Filter for Month selected
xMonth = Sheets("Stats").Range("E37").Value
yMonth = xMonth + 1
xYear = Sheets("Stats").Range("D37").Value
If xMonth = 12 Then
yYear = xYear + 1
Else
yYear = xYear
End If
BegMonth = xMonth & "/1/" & xYear
EndMonth = yMonth & "/1/" & yYear


Sheets("Log").Select
'Selection.AutoFilter Field:=7, Criteria1:="=9/1/2008",
Operator:=xlAnd, _
Criteria2:="<10/1/2008"
Selection.AutoFilter Field:=7, Criteria1:="=" & BegMonth,
Operator:=xlAnd, _
Criteria2:="<" & EndMonth

' Clear old Pivot table area on Work Col a-l
Sheets("Work").Visible = True
Sheets("Work").Select
Range("A4:l500").Clear
Range("A4").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Log!R5C1:R500C10").CreatePivotTable TableDestination:= _
"Work!R4C1", TableName:="PivotTable5", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable5").ColumnGrand = False
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Circuit")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Trainer")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable5").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("Course Hours"), "Sum of Course
Hours", _
xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
Sheets("Work").Visible = False
Application.ScreenUpdating = False
Sheets("Stats").Activate
Range("F37").Select
End Sub


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

hi, !

I am struggling trying to create a pivot table from filtered data.
I am enclosing the sub but am scared to death to do it because I now it is not pristine code.
I believe the problem is that I successfully filter the data but then I select all of the rows for the pivot table
rather than just the filtered rows. Any help is appreciated...

it is not so pristine either, but you might want to give a try to the following (for an existing PT):

- add a column to your source range using something like: =subtotal(2,e2)
put some title such as "visible" -???-

- use the PT-Wizard, backward to include the above (new) column as a page-field (and finish the PTW)

- now, after filtering, you can "play" by refreshing your PT and/or "filtering" over the (new) page-field

hth,
hector.