File Name changes with dates and Deleting Rows of Zeros

A

andrewg10

Joined
Jul 18, 2013
Messages
1
Reaction score
0
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!


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

variable number of rows. 2
Naming variable range selected by cursor movement 2
Macro to fill to next field with data 4
Error using offset 1
Help again!!! 1
Filtering and removing rows 2
Run a procedure in all the sheets of the workbook. 1
VBA Runtime error '1004' 6

Top