VBA code to import new data to dashboard

[JasonMS2022]

Good day all,

I'm attempting to build a dashboard of Incident Ticketing data for our IT office. Every morning I extract a new Incident report from ServiceNow and import that worksheet into the dashboard excel file. I then try to update each row by matching ticket numbers between the new incident report data to the existing dashboard incident data, or if the tickets don't exist in the dashboard then append it to the end of the dataset/table.

So far this is the code I'm trying to use but it is running extremely long (30+ minutes and I finally stopped the code) and I'm only working with about 3000-3500 rows of data (36 columns) not all that huge. Any suggestions on a better method or ways to streamline this code would be appreciated.

Basic requirements: Scan the new dataset ticket numbers, which are in Column A on a worksheet named [Page 1]. Then check the 'new' ticket number against the existing data set tickets, also Column A but on a worksheet named [INCDATA]. If a match is found between the two, copy the matching row of new data from the 'New' [Page 1] over the 'Old' [INCDATA], if no match is found copy the 'New' [Page 1] row of data to the end of the 'Old' [INCDATA] dataset.

Thanks!!
Jason

Sub DataScrub()

    Dim wsNew As Worksheet, wsINC As Worksheet
    Dim lrow As Integer, nrow As Integer, i As Integer
    Dim str As String, str2 As String
    Dim fndrng As Range
        Set wsNew = ThisWorkbook.Sheets("Page 1")
        Set wsINC = ThisWorkbook.Sheets("INCDATA")
        lrow = wsNew.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
        nrow = wsINC.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row + 1
        i = 2
        
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Do While i <= lrow
        Set fndrng = wsINC.Range("A:A").Find(what:=wsNew.Cells(i, 1))
        str = "A" & i & ":AF" & i
        
        If fndrng Is Nothing Then
            str2 = "A" & nrow
            nrow = nrow + 1
            Else
            str2 = "A" & fndrng.Row
        End If
            
        wsNew.Range(str).Copy
        wsINC.Range(str2).PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
                
    Loop

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Here is a second set of code I've also tried but it errors out on the J-Loop after about 500-550 rows...

'  Sub DataScrub2()
'
'    Dim wsNewew As Worksheet, wsINC As Worksheet
'    Dim lrow As Integer, nrow As Integer, i As Integer, j As Integer, rw As Integer
'    Dim fndrng As Range
'    Set wsNewew = ThisWorkbook.Sheets("Page 1")
'    Set wsINC = ThisWorkbook.Sheets("INCDATA")
'    lrow = wsNewew.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row
'    nrow = wsINC.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row + 1
'
'    i = 2
'    Application.ScreenUpdating = False
'    Application.Calculation = xlCalculationManual
'    Do While i <= lrow
'        Set fndrng = wsINC.Range("A:A").Find(what:=wsNewew.Cells(i, 1))
'        If fndrng Is Nothing Then
'            rw = nrow
'            nrow = nrow + 1
'        Else
'            rw = fndrng.Row
'        End If
'        For j = 1 To 36
'            wsINC.Cells(rw, j) = wsNewew.Cells(i, j)
'        Next j
'        wsNewew.Rows(i).Delete
'        lrow = lrow - 1
'    Loop
'    Application.ScreenUpdating = True
'    Application.Calculation = xlCalculationAutomatic
'
' End Sub

 

[JasonMS2022]

Ok, I've decided to go with the second method as it is faster and I found the problem was one of the cells contained an excessively large amount of data causing the formula to fail.