Excel VBA to check for items from previous versions

Question

I am currently creating a VBA that should check, if certain items are available over the next days. The file name is based on the date, e.g. 20210807 (format: yyyymmdd). Each table contains several items, that are not available anymore. Also, there is a row which indicates, if the item has been arrived, but still is not available yet. My requirements are:

• since when the items in the new list have been unavailable (e.g. 05 Aug 2021)
• when the items in the new list have been arrived (e.g. 06 Aug 2021)

So in the file 20210807 there is a possibility, that an item has been in the list for the first time in 05 Aug and the row for "arrival" has been checked in 06 August, but the item is still not available and is therefore in the list.

I have written this code with this functions:

• Create new columns: create 5 columns for new information
• UID: create UID in order to search for the same information in the previous tables
• Date of first appearance: check in the previous day, if the items has been in the list and if so what the date of first appearance is
• Days since first appearance (fa): if there is a date of first appearance then function {today-date of fa}
• Date of arrival: check in the previous day, if the items has been in the list and if there was a date of arrival. If in the previous day there was a date in the column that it should take this date if not it should check if the cell AM from todays file contains "x". If so it should take todays date.
• Days since arrival: if there is a date of arrival then function {today-date of arrival}
• Copy to whole cells: copy function to whole cells and paste it as "value". I faced problems here, if the function stays in the cell forever.

Right now the date of arrival does not work and sometimes I get a #value error. So since I am a beginner with VBA, I thought someone can help me :)

This is my code:

‚Create new columns
    Columns("A:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 
'UID
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "UID"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=RC[5]&RC[6]"
    
‚Date of first appearance
    Range("B1").Select
    ActiveCell.FormulaR1C1 = " Date of first appearance"
    Range("B2").Select
    ActiveCell.Formula2R1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-1],INDIRECT(CONCATENATE(""'"",LEFT(RIGHT(CELL(""Filename""),13),8)-1,"".xlsx'!$A:$AP"")),2,FALSE)),DATE(LEFT(LEFT(RIGHT(CELL(""Filename""),13),8),4),MID(LEFT(RIGHT(CELL(""Filename""),13),8),5,2),RIGHT(LEFT(RIGHT(CELL(""Filename""),13),8),2)),VLOOKUP(RC[-1],INDIRECT(CONCATENATE(""'"",LEFT(RIGHT(CELL(""Filename""),13),8)-1,"".xlsx'!$A:$AP"")),2,FALSE))"
    Selection.NumberFormat = "dd/mm/yyyy"
    
'Days since first appearance
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "'Days since first appearance "
    Range("C2").Select
    ActiveCell.Formula2R1C1 = _
        "=DATE(LEFT(LEFT(RIGHT(CELL(""Filename""),13),8),4),MID(LEFT(RIGHT(CELL(""Filename""),13),8),5,2),RIGHT(LEFT(RIGHT(CELL(""Filename""),13),8),2))-RC[-1]"
    Selection.NumberFormat = "General ""Tag(e)"""
    
‚Date of arrival
    Range("D1").Select
    ActiveCell.FormulaR1C1 = " Date of arrival "
    Range("D2").Select
    ActiveCell.Formula2R1C1 = _
        "=IF(RC[-2]=DATE(LEFT(LEFT(RIGHT(CELL(""Filename""),13),8),4),MID(LEFT(RIGHT(CELL(""Filename""),13),8),5,2),RIGHT(LEFT(RIGHT(CELL(""Filename""),13),8),2)),""n/a"",IF(ISERROR(FIND(""X"",VLOOKUP(RC[-3],INDIRECT(CONCATENATE(""'"",LEFT(RIGHT(CELL(""Filename""),13),8)-1,"".xlsx'!$A:$AP"")),44,FALSE))),IF(ISERROR(FIND(""X"",RC[40])),""/"",DATE(LEFT(LEFT(RIGHT(CELL(""Da" & _
        "teiname""),13),8),4),MID(LEFT(RIGHT(CELL(""Filename""),13),8),5,2),RIGHT(LEFT(RIGHT(CELL(""Filename""),13),8),2))),VLOOKUP(RC[-3],INDIRECT(CONCATENATE(""'"",LEFT(RIGHT(CELL(""Filename""),13),8)-1,"".xlsx'!$A:$AP"")),2,FALSE)))" & _
        ""
    Selection.NumberFormat = "dd/mm/yyyy"
        With Selection
        .HorizontalAlignment = xlRight
    End With
 
‚Days since arrival
    Range("E1").Select
    ActiveCell.FormulaR1C1 = " Days since arrival "
    Range("E2").Select
    ActiveCell.Formula2R1C1 = _
        "=IF(RC[-1]=""/"","""",IF(RC[-1]=""n/a"","""",DATE(LEFT(LEFT(RIGHT(CELL(""Filename""),13),8),4),MID(LEFT(RIGHT(CELL(""Filename""),13),8),5,2),RIGHT(LEFT(RIGHT(CELL(""Filename""),13),8),2))-RC[-1]))"
    Selection.NumberFormat = "General ""Tag(e)"""
        
‚Copy to whole cells
    Range("A2:E2").Select
    Selection.Copy
    Range("F2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(-1, -1).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste