Hey guys, I have a few problems i was hoping i could get some pointers/tips on how to complete my task at hand. I have two inventory files that i open everyday (rbcxxxx and bbxxxx) and need to merge to compare inventories. The amount of rows is never the same but the amount of columns are.
My first problem is, how do i create a macro that will reference bbxxxx (current date ie. bb0718) not a specific file.
So i need this to reference a date not the bb0716 file
Windows("bb0716.xls").Activate
Windows("rbc0716.xls").Activate
My second problem is after i run the code that i have up to now, how do i search the data and delete the ENTIRE row that has a ZERO in column E. After i have deleted those rows, i need to change all #N/As to 0. There are other steps but i can handle those...
Any ideas for me and thanks for all the help!
My first problem is, how do i create a macro that will reference bbxxxx (current date ie. bb0718) not a specific file.
So i need this to reference a date not the bb0716 file
Windows("bb0716.xls").Activate
Windows("rbc0716.xls").Activate
My second problem is after i run the code that i have up to now, how do i search the data and delete the ENTIRE row that has a ZERO in column E. After i have deleted those rows, i need to change all #N/As to 0. There are other steps but i can handle those...
Any ideas for me and thanks for all the help!
Code:
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[/B][/B]]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