Dynamic Pivot Table Ranges Do not Copy Correctly to new Location

[EDN Admin]

I have a Workbook with several worksheets. I want to take the Pivot Table data and Copy the results to another worksheet, then change the Pivto Table Settings and copy the new results to the other worksheet.
Im new to macros and VB so I know Im probably just missing some little thing. When I run the following Macro/VB the # of rows that get copied from the Pivot Table to the other Worksheet always end up being the same; even though the first view of
the Pivot Table has more rows, then the second view. For the life of me I cant figure out what Ive done worng for it to do that.
Sub B_PopulateWTAreaChartDataBatteries()
Sheets("WT Pivot Table").Select<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("GROUP").CurrentPage = "Batteries"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("GBU").CurrentPage = "(All)"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("Sector").CurrentPage = "(All)"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("Category").CurrentPage = "(All)"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("Sub Category").CurrentPage = "(All)"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("Type").CurrentPage = "(All)"<br/>
<br/>
Dim myR As Long<br/>
myR = Cells(Rows.Count, 2).End(xlUp).Row
Range("C8").Value = "% of Total"<br/>
Range("D8").Value = "Cum % of Total"<br/>
Range("C9").FormulaR1C1 = "=SUM(R[1]C[-1]:R[" & myR - 9 & "]C[-1])"<br/>
Range("C10:C" & myR).FormulaR1C1 = "=+RC[-1]/R9C3"<br/>
Range("D10" & myR).FormulaR1C1 = "=+RC[-1]+R[-1]C"<br/>
<br/>
Range("D10").Select<br/>
Range(Selection, Selection.End(xlDown)).Select<br/>
Selection.Copy<br/>
Sheets("WT Area Chart Data").Select<br/>
Range("C7").Select<br/>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<br/>
:=False, Transpose:=False<br/>
<br/>
Sheets("WT Pivot Table").Select<br/>
Range("C").Select<br/>
Range(Selection, Selection.End(xlDown)).Select<br/>
Selection.ClearContents
ActiveSheet.PivotTables("PivotData").PivotFields("GROUP").CurrentPage = "Batteries"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("GBU").CurrentPage = "(All)"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("Sector").CurrentPage = "(All)"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("Category").CurrentPage = "Gen Purpose Batt"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("Sub Category").CurrentPage = "(All)"<br/>
ActiveSheet.PivotTables("PivotData").PivotFields("Type").CurrentPage = "(All)"
Range("C8").Value = "% of Total"<br/>
Range("D8").Value = "Cum % of Total"<br/>
Range("C9").FormulaR1C1 = "=SUM(R[1]C[-1]:R[" & myR - 9 & "]C[-1])"<br/>
Range("C10:C" & myR).FormulaR1C1 = "=+RC[-1]/R9C3"<br/>
Range("D10" & myR).FormulaR1C1 = "=+RC[-1]+R[-1]C"
Range("D10").Select<br/>
Range(Selection, Selection.End(xlDown)).Select<br/>
Selection.Copy<br/>
<br/>
Sheets("WT Area Chart Data").Select<br/>
Range("D7").Select<br/>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<br/>
:=False, Transpose:=False<br/>
<br/>
Sheets("WT Pivot Table").Select
Range("C").Select<br/>
Range(Selection, Selection.End(xlDown)).Select<br/>
Selection.ClearContents
End Sub <hr class="sig Lisa

View the full article