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

Sub Macro3()

'

' Macro3 Macro

'

'

Sheets("Feeder").Select

Cells.Select

Application.CutCopyMode = False

Selection.AutoFilter

Sheets("GL").Select

Cells.Select

Selection.AutoFilter

Sheets("Feeder").Select

ActiveSheet.Range("$A$1:$DU$388").AutoFilter Field:=117, Criteria1:=

"121557"

Range("DM211").Select

Selection.Copy

Sheets("GL").Select

Range("FG1").Select

Sheets("Feeder").Select

Windows("187803.csv").Activate

Range("DC384").Select

Windows("FeederGLfy18p12unknown.csv").Activate

ActiveWindow.SmallScroll Down:=-15

Sheets("FeederGLfy18p12unknown").Select

Windows("workbook creator.xlsx").Activate

Windows("187803.csv").Activate

Windows("workbook creator.xlsx").Activate

Sheets("Feeder").Select

Windows("FeederGLfy18p12unknown.csv").Activate

ActiveSheet.Range("$A$1:$DU$388").AutoFilter Field:=104, Criteria1:=

"-13328.42"

Cells.Select

Range("CV1").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets.Add After:=ActiveSheet

Sheets("Sheet7").Select

Sheets("Sheet7").Name = "Feeder2"

Range("A1").Select

ActiveSheet.Paste

Sheets.Add After:=ActiveSheet

Sheets("Sheet8").Select

Sheets("Sheet8").Name = "GL2"

Range("E21").Select

Sheets("GL").Select

Sheets("Feeder").Select

Range("DF408").Select

Application.CutCopyMode = False

Range("DC415").Select

Sheets("Feeder").Select

Range("CZ210").Select

Selection.Copy

Sheets("GL2").Select

ActiveWindow.SmallScroll Down:=-42

Sheets("GL").Select

ActiveWindow.SmallScroll Down:=-9

ActiveSheet.Range("$A$1:$FQ$314750").AutoFilter Field:=150, Criteria1:=

"-13328.42"

Cells.Select

Range("EJ1").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("GL2").Select

Range("A1").Select

ActiveSheet.Paste

Sheets.Add After:=ActiveSheet

Sheets("Sheet9").Select

Sheets("Sheet9").Name = "Pivot2 "

Range("A2").Select

Application.CutCopyMode = False

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=

"Feeder2!R1C1:R20000C125", Version:=6).CreatePivotTable TableDestination:=

"Pivot2 !R2C1", TableName:="PivotTable3", DefaultVersion:=6

Sheets("Pivot2 ").Select

Cells(2, 1).Select

Range("B6").Select

ActiveWindow.SmallScroll Down:=-9

With ActiveSheet.PivotTables("PivotTable3").PivotFields("signedamount")

.Orientation = xlRowField

.Position = 1

End With

ActiveWindow.SmallScroll Down:=-3

With ActiveSheet.PivotTables("PivotTable3").PivotFields("mainaccount")

.Orientation = xlRowField

.Position = 2

End With

With ActiveSheet.PivotTables("PivotTable3").PivotFields("limit")

.Orientation = xlRowField

.Position = 3

End With

With ActiveSheet.PivotTables("PivotTable3").PivotFields("fiscalperiod")

.Orientation = xlRowField

.Position = 4

End With

ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables(

"PivotTable3").PivotFields("signedamount"), "Count of signedamount",

xlCount

With ActiveSheet.PivotTables("PivotTable3").PivotFields("begfy")

.Orientation = xlRowField

.Position = 3

End With

With ActiveSheet.PivotTables("PivotTable3").PivotFields("aai")

.Orientation = xlRowField

.Position = 1

End With

With ActiveSheet.PivotTables("PivotTable3").PivotFields("voucher")

.Orientation = xlRowField

.Position = 5

End With

With ActiveSheet.PivotTables("PivotTable3").PivotFields("voucher")

.Orientation = xlRowField

.Position = 7

End With

Range("D2").Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=

"GL2!R1C1:R2000C172", Version:=6).CreatePivotTable TableDestination:=

"Pivot2 !R2C4", TableName:="PivotTable4", DefaultVersion:=6

Sheets("Pivot2 ").Select

Cells(2, 4).Select

Range("E8").Select

ActiveWindow.SmallScroll Down:=-9

With ActiveSheet.PivotTables("PivotTable4").PivotFields("voucher")

.Orientation = xlRowField

.Position = 1

End With

ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables(

"PivotTable4").PivotFields("signedamount"), "Count of signedamount",

xlCount

With ActiveSheet.PivotTables("PivotTable4").PivotFields("signedamount")

.Orientation = xlRowField

.Position = 2

End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("mainaccount")

.Orientation = xlRowField

.Position = 3

End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("limit")

.Orientation = xlRowField

.Position = 4

End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("fiscalperiod")

.Orientation = xlRowField

.Position = 2

End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("begfy")

.Orientation = xlRowField

.Position = 2

End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("aai")

.Orientation = xlRowField

.Position = 2

End With

End Sub

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>