Seeking Guidance/ Help with my Macros/VBA Code (File name changes with what date it is)

Hello All, BIG time newbie here to the macros/VBA world so bear with my lack of sufficient knowledge. I have to do an inventory report everyday for work that is constantly changing. Multiple problems:
1) I do not know how to make an array stop at the last row (there will never be the same amount of rows on any given day). Example:
Selection.AutoFill Destination:=Range("C2:C304") ---> this needs to be Range("C2:Last row") and i do not know what that specific code is. There are multiple instances in the code where I need to change it to be the last row and not a specific cell.
2)I will like to delete just the last four rows of the BB0716 file sheet 4 (the pivot table) in E281:F284 . Again, this will not always be its place holder as it could be higher or lower in the excel sheet but ALWAYS the last 4
3) I will always have an excel window open with the two excel workbooks open of RBCxxxx and BBxxxx that will look like the base models attached. My problem is how do i make 1 macro to reference a file that changes names constantly? For instance tomorrow will be rbc0717 next day rbc 0718 etc. The file is always saved as the previous WORKING (M-F) day's date.
4) At the end of this code i am not done with my process, i just don't have a clue what to do next. This is what i need to do:

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