How to export missing numbers into a separate worksheet

[tbeynon1]

Here is some code that highlights any missing for any missing numbers in column A.

'Finding missing report numbers
Dim inc As Integer
Dim inc2 As Integer
Set myrng = Range("A2:A10000")
    myrng.Interior.ColorIndex = xlNone
Set myrng = Range("A3:A10000")
    clr = 3
    inc = Range("A2").Value
    
For Each cel In myrng
        If cel.Value <> (inc + 1) Then
            cel.Interior.ColorIndex = clr
        End If
            inc = cel.Value
    If cel.Value = "" Then
        Exit Sub
    End If
Next

I was wondering if you could change it from highlighting to copying the missing numbers into a new spread sheet.

 

[Fishboy]

Here is some code that highlights any missing for any missing numbers in column A.

'Finding missing report numbers
Dim inc As Integer
Dim inc2 As Integer
Set myrng = Range("A2:A10000")
    myrng.Interior.ColorIndex = xlNone
Set myrng = Range("A3:A10000")
    clr = 3
    inc = Range("A2").Value
    
For Each cel In myrng
        If cel.Value <> (inc + 1) Then
            cel.Interior.ColorIndex = clr
        End If
            inc = cel.Value
    If cel.Value = "" Then
        Exit Sub
    End If
Next

I was wondering if you could change it from highlighting to copying the missing numbers into a new spread sheet.

Hi tbeynon1,

Assuming you want a list generated on Sheet2 of the current workbook then you could try the following modification to your code:

'Finding missing report numbers
Dim inc As Integer
Dim inc2 As Integer
Dim LastRow As Long

Sheets("Sheet2").Range("A1").Value = "Missing Report Numbers"
LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1


Set myrng = Range("A2:A10000")
    myrng.Interior.ColorIndex = xlNone
Set myrng = Range("A3:A10000")
    clr = 3
    inc = Range("A2").Value
    
For Each cel In myrng
        If cel.Value <> (inc + 1) Then
            Sheets("Sheet2").Range("A" & LastRow).Value = cel.Value
            LastRow = LastRow + 1
        End If
            inc = cel.Value
    If cel.Value = "" Then
        Exit Sub
    End If
Next