Sub positions()
'
' positions Macro
'
' Keyboard Shortcut: Ctrl+p
'
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FTR23_Daily_Inventory_Report!R1C1:R304C2", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable6" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable6").PivotFields("CUSIP")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("Net Position"), "Sum of Net Position", xlSum
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlUp).Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("E5").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "RBCvBB"
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CUSIP"
Range("C3").Select
ActiveCell.FormulaR1C1 = "RBC"
Range("D3").Select
ActiveCell.FormulaR1C1 = "BB"
Range("E3").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "VAR"
Range("D4").Select
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "BBvRBC"
Range("B4").Select
ActiveWindow.WindowState = xlMinimized
Windows("bb0716.xls").Activate
ActiveWindow.WindowState = xlMaximized
Columns("A:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Range("A227").Select
Selection.End(xlUp).Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R289C2", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable7", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable7").PivotFields("CusipNumber")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("CurrentNetPosition"), "Sum of CurrentNetPosition", _
xlSum
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlUp).Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E5").Select
Selection.End(xlDown).Select
Range("H282").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "delete last four fows"
Range("E284").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("rbc0716.xls").Activate
ActiveWindow.WindowState = xlMaximized
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CUSIP"
Range("C3").Select
ActiveCell.FormulaR1C1 = "BB"
Range("D3").Select
ActiveCell.FormulaR1C1 = "RBC"
Range("E3").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "VAR"
Range("D4").Select
Sheets("RBCvBB").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],[bb0716.xls]Sheet4!R5C5:R284C6,2,FALSE)"
Range("D4").Select
Selection.AutoFill Destination:=Range("D4:D280")
Range("D4:D280").Select
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("E4").Select
Selection.AutoFill Destination:=Range("E4:E280")
Range("E4:E280").Select
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("RBCvBB").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RBCvBB").Sort.SortFields.Add Key:=Range("E4:E280") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("RBCvBB").Sort
.SetRange Range("B3:E280")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("F10").Select
Sheets("BBvRBC").Select
Range("D4").Select
ActiveWindow.WindowState = xlMinimized
Windows("bb0716.xls").Activate
ActiveWindow.WindowState = xlMaximized
Range("E284").Select
Selection.End(xlUp).Select
Range("E5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("rbc0716.xls").Activate
ActiveWindow.WindowState = xlMaximized
Range("D4").Select
Range("D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R5C5:R281C6,2,FALSE)"
Range("D4").Select
Selection.AutoFill Destination:=Range("D4:D283")
Range("D4:D283").Select
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("E4").Select
Selection.AutoFill Destination:=Range("E4:E283")
Range("E4:E283").Select
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("BBvRBC").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BBvRBC").Sort.SortFields.Add Key:=Range("E4:E283") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("BBvRBC").Sort
.SetRange Range("B3:E283")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("F14").Select
End Sub
Bookmarks