trying to create a dynamic pivot table

[jordanburch]

Hey guys,

Ive basically recorded this macro and want to make it dynamic to sort through lots of data. Basically I want to filter the data on one tab and limit it to one voucher and that voucher should have only one signedamount. Then I want to copy and paste that data onto a new tab that has the same name as the data so it will be limited to the first line of data on the feeder side with only one signed amount. Then I want to search for that signed amount that I used in the first set of data and filter my signedamount on the GL side with that same singned amount and take that and copy and paste it into a new GL2 tab. Then I want to create a new tab called Pivot2 that creats pivot tables off off the feeder and gl tabs side by side. That should show the differences between the data sets. I recorded a macro and hopefully it makes sense what I am asking. Please let me know if you need any more info.

Jordan

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Sub Macro3()[/TD]
[/TR]
[TR]
[TD]'[/TD]
[/TR]
[TR]
[TD]' Macro3 Macro[/TD]
[/TR]
[TR]
[TD]'[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]'[/TD]
[/TR]
[TR]
[TD] Sheets("Feeder").Select[/TD]
[/TR]
[TR]
[TD] Cells.Select[/TD]
[/TR]
[TR]
[TD] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD] Selection.AutoFilter[/TD]
[/TR]
[TR]
[TD] Sheets("GL").Select[/TD]
[/TR]
[TR]
[TD] Cells.Select[/TD]
[/TR]
[TR]
[TD] Selection.AutoFilter[/TD]
[/TR]
[TR]
[TD] Sheets("Feeder").Select[/TD]
[/TR]
[TR]
[TD] ActiveSheet.Range("$A$1:$DU$388").AutoFilter Field:=117, Criteria1:= [/TD]
[/TR]
[TR]
[TD] "121557"[/TD]
[/TR]
[TR]
[TD] Range("DM211").Select[/TD]
[/TR]
[TR]
[TD] Selection.Copy[/TD]
[/TR]
[TR]
[TD] Sheets("GL").Select[/TD]
[/TR]
[TR]
[TD] Range("FG1").Select[/TD]
[/TR]
[TR]
[TD] Sheets("Feeder").Select[/TD]
[/TR]
[TR]
[TD] Windows("187803.csv").Activate[/TD]
[/TR]
[TR]
[TD] Range("DC384").Select[/TD]
[/TR]
[TR]
[TD] Windows("FeederGLfy18p12unknown.csv").Activate[/TD]
[/TR]
[TR]
[TD] ActiveWindow.SmallScroll Down:=-15[/TD]
[/TR]
[TR]
[TD] Sheets("FeederGLfy18p12unknown").Select[/TD]
[/TR]
[TR]
[TD] Windows("workbook creator.xlsx").Activate[/TD]
[/TR]
[TR]
[TD] Windows("187803.csv").Activate[/TD]
[/TR]
[TR]
[TD] Windows("workbook creator.xlsx").Activate[/TD]
[/TR]
[TR]
[TD] Sheets("Feeder").Select[/TD]
[/TR]
[TR]
[TD] Windows("FeederGLfy18p12unknown.csv").Activate[/TD]
[/TR]
[TR]
[TD] ActiveSheet.Range("$A$1:$DU$388").AutoFilter Field:=104, Criteria1:= [/TD]
[/TR]
[TR]
[TD] "-13328.42"[/TD]
[/TR]
[TR]
[TD] Cells.Select[/TD]
[/TR]
[TR]
[TD] Range("CV1").Activate[/TD]
[/TR]
[TR]
[TD] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD] Selection.Copy[/TD]
[/TR]
[TR]
[TD] Sheets.Add After:=ActiveSheet[/TD]
[/TR]
[TR]
[TD] Sheets("Sheet7").Select[/TD]
[/TR]
[TR]
[TD] Sheets("Sheet7").Name = "Feeder2"[/TD]
[/TR]
[TR]
[TD] Range("A1").Select[/TD]
[/TR]
[TR]
[TD] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD] Sheets.Add After:=ActiveSheet[/TD]
[/TR]
[TR]
[TD] Sheets("Sheet8").Select[/TD]
[/TR]
[TR]
[TD] Sheets("Sheet8").Name = "GL2"[/TD]
[/TR]
[TR]
[TD] Range("E21").Select[/TD]
[/TR]
[TR]
[TD] Sheets("GL").Select[/TD]
[/TR]
[TR]
[TD] Sheets("Feeder").Select[/TD]
[/TR]
[TR]
[TD] Range("DF408").Select[/TD]
[/TR]
[TR]
[TD] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD] Range("DC415").Select[/TD]
[/TR]
[TR]
[TD] Sheets("Feeder").Select[/TD]
[/TR]
[TR]
[TD] Range("CZ210").Select[/TD]
[/TR]
[TR]
[TD] Selection.Copy[/TD]
[/TR]
[TR]
[TD] Sheets("GL2").Select[/TD]
[/TR]
[TR]
[TD] ActiveWindow.SmallScroll Down:=-42[/TD]
[/TR]
[TR]
[TD] Sheets("GL").Select[/TD]
[/TR]
[TR]
[TD] ActiveWindow.SmallScroll Down:=-9[/TD]
[/TR]
[TR]
[TD] ActiveSheet.Range("$A$1:$FQ$314750").AutoFilter Field:=150, Criteria1:= [/TD]
[/TR]
[TR]
[TD] "-13328.42"[/TD]
[/TR]
[TR]
[TD] Cells.Select[/TD]
[/TR]
[TR]
[TD] Range("EJ1").Activate[/TD]
[/TR]
[TR]
[TD] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD] Selection.Copy[/TD]
[/TR]
[TR]
[TD] Sheets("GL2").Select[/TD]
[/TR]
[TR]
[TD] Range("A1").Select[/TD]
[/TR]
[TR]
[TD] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD] Sheets.Add After:=ActiveSheet[/TD]
[/TR]
[TR]
[TD] Sheets("Sheet9").Select[/TD]
[/TR]
[TR]
[TD] Sheets("Sheet9").Name = "Pivot2 "[/TD]
[/TR]
[TR]
[TD] Range("A2").Select[/TD]
[/TR]
[TR]
[TD] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD] ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= [/TD]
[/TR]
[TR]
[TD] "Feeder2!R1C1:R20000C125", Version:=6).CreatePivotTable TableDestination:= [/TD]
[/TR]
[TR]
[TD] "Pivot2 !R2C1", TableName:="PivotTable3", DefaultVersion:=6[/TD]
[/TR]
[TR]
[TD] Sheets("Pivot2 ").Select[/TD]
[/TR]
[TR]
[TD] Cells(2, 1).Select[/TD]
[/TR]
[TR]
[TD] Range("B6").Select[/TD]
[/TR]
[TR]
[TD] ActiveWindow.SmallScroll Down:=-9[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable3").PivotFields("signedamount")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 1[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] ActiveWindow.SmallScroll Down:=-3[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable3").PivotFields("mainaccount")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 2[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable3").PivotFields("limit")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 3[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable3").PivotFields("fiscalperiod")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 4[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( [/TD]
[/TR]
[TR]
[TD] "PivotTable3").PivotFields("signedamount"), "Count of signedamount", [/TD]
[/TR]
[TR]
[TD] xlCount[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable3").PivotFields("begfy")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 3[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable3").PivotFields("aai")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 1[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable3").PivotFields("voucher")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 5[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable3").PivotFields("voucher")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 7[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] Range("D2").Select[/TD]
[/TR]
[TR]
[TD] ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= [/TD]
[/TR]
[TR]
[TD] "GL2!R1C1:R2000C172", Version:=6).CreatePivotTable TableDestination:= [/TD]
[/TR]
[TR]
[TD] "Pivot2 !R2C4", TableName:="PivotTable4", DefaultVersion:=6[/TD]
[/TR]
[TR]
[TD] Sheets("Pivot2 ").Select[/TD]
[/TR]
[TR]
[TD] Cells(2, 4).Select[/TD]
[/TR]
[TR]
[TD] Range("E8").Select[/TD]
[/TR]
[TR]
[TD] ActiveWindow.SmallScroll Down:=-9[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable4").PivotFields("voucher")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 1[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( [/TD]
[/TR]
[TR]
[TD] "PivotTable4").PivotFields("signedamount"), "Count of signedamount", [/TD]
[/TR]
[TR]
[TD] xlCount[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable4").PivotFields("signedamount")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 2[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable4").PivotFields("mainaccount")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 3[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable4").PivotFields("limit")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 4[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable4").PivotFields("fiscalperiod")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 2[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable4").PivotFields("begfy")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 2[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] With ActiveSheet.PivotTables("PivotTable4").PivotFields("aai")[/TD]
[/TR]
[TR]
[TD] .Orientation = xlRowField[/TD]
[/TR]
[TR]
[TD] .Position = 2[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]