VBA Run time error - 6 - Overflow

[Guinaba]

Hi guys,

I am running the code below to copy data from one sheet to another, every day. However, the code start failing with overflow error. The file I am copying the data to has 2Mb the data goes up to row 39700, so I dont think is data issue yet. The code is failing when it finds the last row to copy the data. It looks like I don't have enough memory to run the code? Should I use array to avoid this issue?

Error in the code:

Sub PlannedOrdersHistory()

    Application.ScreenUpdating = False

    Dim CurrDate As Date
    CurrDate = Date
    Dim CurrFileName As String
    CurrFileName = Format(CurrDate, "yyyymmdd") & "_ED_Fcst_VS_Planned_Orders_V10" & ".XLSM"
    Dim Wb1 As Workbook: Set Wb1 = Workbooks(CurrFileName)
    Dim Wb2 As Workbook: Set Wb2 = Workbooks.Open("C:\Users\gnassifb\OneDrive - Lion Pty Ltd\Documents\ED\ED Planned Orders\ED_Planned_Orders_History_1.xlsx")
    Dim LRow As Integer
    Dim SearchString As String
    Dim SearchRange As Range
    LastDate = WorksheetFunction.Max(Wb2.Worksheets("ED_Planned_Orders_History_1").Range("A:A"))

    With Wb2.Worksheets("ED_Planned_Orders_History_1")
      'Find the last non-blank cell in column A(1)
       LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    End With

    'Check if the data is already copied
    If LastDate <> Date Then
       Wb1.Worksheets("EDPlannedOrders").ListObjects("tPlannedOrdersWeekly").DataBodyRange.Copy
       Wb2.Worksheets("ED_Planned_Orders_History_1").Range("A" & LRow + 1).PasteSpecial xlPasteValuesAndNumberFormats
    Else
        MsgBox "Data is already copied", vbExclamation: Exit Sub

    End If

    'Wb2.Close SaveChanges:=True
    Application.CutCopyMode = False

 End Sub

 

[AlphaFrog]

"Dim LRow As Integer"
The max value for an Integer is = 32767

Declare LRow as long

Dim LRow As Long