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:

Please Login or Register  to view this content.