Last Row not being identified correctly

The following macro works great if I run it the way it is intended to be run which is workbook "XXX XXXX Master Workbook.xlsm" contains this macro and a sheet called "Dump"

Every Monday i get a spreadsheet called ABC.

I run this macro and it presents me with the data that I can work with.

I cannot combine multiple weeks of data on one Dump sheet because this macro keeps referencing and in turn overwriting the existing data.

I tries to create a new last row code to replace the specific start point of F2 but I think it is great than that.

Hopefully someone can provide me with the correct lines of code without requiring a sample since this code references at least one other workbook that I wouldn't be able to post, at least publicly.

Option Explicit

Sub Final()
    Dim LR1 As Long
    Dim ColFrom As Long
    Dim ColTo As Long
    Dim X As Integer
       
    ActiveSheet.Name = "ABC"

With Sheets("ABC")
 Range("E8").Copy Workbooks("XXX XXXX Master Workbook.xlsm").Sheets("Dump").Range("F2")
 End With

With Workbooks("XXX XXXX Master Workbook.xlsm").Sheets("Dump").UsedRange
        .Cells.Value = .Cells.Value
        End With

    For X = 1 To 4
        ColFrom = Choose(X, 2, 4, 7, 8)
        ColTo = Choose(X, 3, 1, 8, 11)
        With Sheets("ABC")
            LR1 = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range(.Cells(13, ColFrom), .Cells(LR1, ColFrom)).Copy Workbooks("XXX XXXX Master Workbook.xlsm").Sheets("Dump").Cells(2, ColTo)
            .Columns(ColTo).EntireColumn.AutoFit
        End With
    Next X
    With Workbooks("XXX XXXX Master Workbook.xlsm").Sheets("Dump").UsedRange
        .Cells.Value = .Cells.Value
    End With

Workbooks.Open Filename:= _
        "C:\Users\XXXXX\Desktop\XXX\Customers\XXX XXXX\Reports\XX Report Macros\VLData1.xlsx"
    Windows("XXX XXXX Master Workbook.xlsm").Activate
    Sheets("Dump").Select

With Sheets("Dump")
    Range("B2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],[VLData1.xlsx]VLData1!R2C1:R77C2,2,FALSE)"
    
    Range("D2").FormulaR1C1 = _
        "=VLOOKUP(RC[-3],[VLData1.xlsx]VLData1!R2C1:R77C3,3,FALSE)"
    
    Range("E2").FormulaR1C1 = _
        "=VLOOKUP(RC[-4],[VLData1.xlsx]VLData1!R2C1:R77C4,4,FALSE)"
    
    Range("G2").FormulaR1C1 = "=INT((RC[-1]-DATE(2010,2,1)-WEEKDAY(RC[-1],1))/7)+2"
    
    Range("I2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],[VLData1.xlsx]VLData1!R2C6:R1376C7,2,FALSE)"
    
    Range("J2").FormulaR1C1 = _
        "=VLOOKUP(RC[-2],[VLData1.xlsx]VLData1!R2C6:R1376C8,3,FALSE)"

  LR1 = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range(.Cells(3, "B"), .Cells(LR1, "B")).FormulaR1C1 = .Cells(2, "B").FormulaR1C1
        .Range(.Cells(3, "D"), .Cells(LR1, "D")).FormulaR1C1 = .Cells(2, "D").FormulaR1C1
        .Range(.Cells(3, "E"), .Cells(LR1, "E")).FormulaR1C1 = .Cells(2, "E").FormulaR1C1
        .Range(.Cells(3, "F"), .Cells(LR1, "F")).FormulaR1C1 = .Cells(2, "F").FormulaR1C1
        .Range(.Cells(3, "G"), .Cells(LR1, "G")).FormulaR1C1 = .Cells(2, "G").FormulaR1C1
        .Range(.Cells(3, "I"), .Cells(LR1, "I")).FormulaR1C1 = .Cells(2, "I").FormulaR1C1
        .Range(.Cells(3, "J"), .Cells(LR1, "J")).FormulaR1C1 = .Cells(2, "J").FormulaR1C1
    End With
    
   Columns("A:A").NumberFormat = "0"
   Columns("F:F").NumberFormat = "m/d/yyyy"
    
 With Columns("A:K")
    With .Font
        .Name = "Calibri"
        .Size = 11
    End With
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    With .Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Columns("A:K").EntireColumn.AutoFit
End With

    Windows("VLData1.xlsx").Close

End Sub

Dave,

Thanks for the reply.

I believe I made an error in my first post by only identifying one Last Row which is why this still is not doing what I need it to do. I made some notes within the code below.

Option Explicit

Sub Final()

    Dim LR As Long      'This LR is located in the code starting with For X = 1 To 4

    Dim LR1 As Long    
    Dim ColFrom As Long
    Dim ColTo As Long
    Dim X As Integer
       
    ActiveSheet.Name = "ABC"

With Sheets("ABC")
 Range("E8").Copy Workbooks("XXX XXXX Master Workbook.xlsm").Sheets("Dump").Range("F2")
 End With

With Workbooks("XXX XXXX Master Workbook.xlsm").Sheets("Dump").UsedRange
        .Cells.Value = .Cells.Value
        End With

' The LR I forgot to include is below and I believe this is the one that needs to be changed so that everytime get a new ABC sheet and run the macro it will paste the data on the next available blank row

    For X = 1 To 4
        ColFrom = Choose(X, 2, 4, 7, 8)
        ColTo = Choose(X, 3, 1, 8, 11)
        With Sheets("ABC")
            LR = .Cells(.Rows.Count, 1).End(xlUp).Row 
            .Range(.Cells(13, ColFrom), .Cells(LR1, ColFrom)).Copy Workbooks("XXX XXXX Master Workbook.xlsm").Sheets("Dump").Cells(2, ColTo)
            .Columns(ColTo).EntireColumn.AutoFit
        End With
    Next X
    With Workbooks("XXX XXXX Master Workbook.xlsm").Sheets("Dump").UsedRange
        .Cells.Value = .Cells.Value
    End With

Workbooks.Open Filename:= _
        "C:\Users\XXXXX\Desktop\XXX\Customers\XXX XXXX\Reports\XX Report Macros\VLData1.xlsx"
    Windows("XXX XXXX Master Workbook.xlsm").Activate
    Sheets("Dump").Select

With Sheets("Dump")
    Range("B2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],[VLData1.xlsx]VLData1!R2C1:R77C2,2,FALSE)"
    
    Range("D2").FormulaR1C1 = _
        "=VLOOKUP(RC[-3],[VLData1.xlsx]VLData1!R2C1:R77C3,3,FALSE)"
    
    Range("E2").FormulaR1C1 = _
        "=VLOOKUP(RC[-4],[VLData1.xlsx]VLData1!R2C1:R77C4,4,FALSE)"
    
    Range("G2").FormulaR1C1 = "=INT((RC[-1]-DATE(2010,2,1)-WEEKDAY(RC[-1],1))/7)+2"
    
    Range("I2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],[VLData1.xlsx]VLData1!R2C6:R1376C7,2,FALSE)"
    
    Range("J2").FormulaR1C1 = _
        "=VLOOKUP(RC[-2],[VLData1.xlsx]VLData1!R2C6:R1376C8,3,FALSE)"

  LR1 = worksheets("Dump").Range("A" & .Rows.Count).End(xlUp).Row
        .Range(.Cells(3, "B"), .Cells(LR1, "B")).FormulaR1C1 = .Cells(2, "B").FormulaR1C1
        .Range(.Cells(3, "D"), .Cells(LR1, "D")).FormulaR1C1 = .Cells(2, "D").FormulaR1C1
        .Range(.Cells(3, "E"), .Cells(LR1, "E")).FormulaR1C1 = .Cells(2, "E").FormulaR1C1
        .Range(.Cells(3, "F"), .Cells(LR1, "F")).FormulaR1C1 = .Cells(2, "F").FormulaR1C1
        .Range(.Cells(3, "G"), .Cells(LR1, "G")).FormulaR1C1 = .Cells(2, "G").FormulaR1C1
        .Range(.Cells(3, "I"), .Cells(LR1, "I")).FormulaR1C1 = .Cells(2, "I").FormulaR1C1
        .Range(.Cells(3, "J"), .Cells(LR1, "J")).FormulaR1C1 = .Cells(2, "J").FormulaR1C1
    End With
    
   Columns("A:A").NumberFormat = "0"
   Columns("F:F").NumberFormat = "m/d/yyyy"
    
 With Columns("A:K")
    With .Font
        .Name = "Calibri"
        .Size = 11
    End With
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    With .Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Columns("A:K").EntireColumn.AutoFit
End With

    Windows("VLData1.xlsx").Close

End Sub