Dynamic Pivot and Multiple Related Calculations Updates

[tlc_in_OK]

I have a very unique spreadsheet (XL2007) that requires multiple dynamic updates, but with very specific criteria, in order to present a graph for analysis to end users. I am NOT a VBA programmer, and normally I would write everything in SQL stored procs, but the users need to be disconnected from the server after the initial load of data.

I need a pivot table ("Pivot" worksheet) to refresh when the data in "Properties" worksheet is refreshed. But I also need to filter and copy the pivot results to other columns on "Pivot" in order to run very specific calculations on the pivot results (in still more columns on "Pivot"). I tried to start with a recorded macro, and make adjustments for the varying number of rows, etc., but I'm just not knowledgable enough in VBA. I'm including the recorded code, so you can get a general idea of what I'm doing. Hopefully someone can help me...I've been working on this five days now and have made very little progress!

The formulas I'm "filling" down columns I through L are as follows, and of course, they fill the dynamic number of rows copied into F through H:

I is a running total of column H, starting at I2 (and H2)
J is a % of Total of column I ( =I2/(SUM(H$2:H$379)) )
K is an IF statement based on the status copied into $G ( =IF($G2="Accepted",F2,"") )
L is a different IF statement based on the status in $G ( =IF($G2="","",(IF($G2="Accepted",-0.2,F2))) )


Sub GraphingCalcs()
'
' GraphingCalcs Macro
'
' Keyboard Shortcut: Ctrl+g
'
Range("F2:H203").Select
Selection.ClearContents
Range("A1").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Range("B2").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status")
.PivotItems("No Bid-No Sale").Visible = False
.PivotItems("Rejected").Visible = False
End With
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status")
.PivotItems("Accepted").Visible = False
.PivotItems("No Bid-No Sale").Visible = True
.PivotItems("Rejected").Visible = True
End With
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F98").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Previous.Select
Sheets("Pivot").Select
Range("I2").Select
Application.CutCopyMode = False
Range("I3").Select
Selection.AutoFill Destination:=Range("I3:I202"), Type:=xlFillCopy
Range("I3:I202").Select
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J202"), Type:=xlFillCopy
Range("J2:J202").Select
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K96"), Type:=xlFillCopy
Range("K2:K96").Select
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L202"), Type:=xlFillCopy
Range("L2:L202").Select
End Sub

 

[tlc_in_OK]

****One additional note---the filtered pivot data must be pasted in a very specific order for the graph to work as required, i.e. I paste data filtered for Status="Accepted" into F2 (over to H2 and down), the next set is filtered for all other statuses, and HAS to be pasted immediately below the previous set (whichever row it may end in) with one blank row in between. And I still have to get the running total to continue to total, bypassing the blank row. Now you see what I'm up against! Thanks to anyone that can save me!!

 

[tlc_in_OK]

Ok, I've managed to create a procedure to copy the first set of data from the pivot table to the right place.


Sub CopyPivotInfo1()
Dim RngPS As Range, RngPCR As Range, Rng As Range, Last As Long
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status")
.PivotItems("Accepted").Visible = True
.PivotItems("No Bid-No Sale").Visible = False
.PivotItems("Rejected").Visible = False
With Sheets("Pivot")
Set RngPS = .Range(.Range("A6"), .Range("C" & Rows.Count).End(xlUp))
End With

With Sheets("Pivot")
Set Rng = .Range(.Range("F2"), .Range("H" & Rows.Count).End(xlUp))
Rng.Resize(, 6).ClearContents
RngPS.Copy .Range("F2")
Last = .Range("H" & Rows.Count).End(xlUp).Row
End With

End With
End Sub

Then I created a similar procedure that copies the second set of filtered pivot table data, but I don't know how to identify "the end of the previous pasted set plus one empty row" as the destination.

Sub CopyPivotInfo2()
Dim RngPS As Range, Rng As Range, Last As Long
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status")
.PivotItems("Accepted").Visible = False
.PivotItems("No Bid-No Sale").Visible = True
.PivotItems("Rejected").Visible = True
With Sheets("Pivot")
Set RngPS = .Range(.Range("A6"), .Range("C" & Rows.Count).End(xlUp))
End With

With Sheets("Pivot")
---What comes after this to paste at the bottom of the previously pasted data?