Deleting column data when cell value that = page name not found

[mystic_muffin]

Hey, Friends;

I have this bit of code that I'm working with that looks through my worksheets and copies data from any worksheet that has a numeric name (job number). It fills a 'master' sheet.

I'm also run a script to delete these worksheets when the job is complete.

Right now to delete column data from my 'master', I'm comparing the values in row 1 vs a hidden table of contents and doing a countif, then deleting anything with a 0 in the master row 2... which is obviously very inefficient.

Is there an easier way to delete the column data if the cell data in row 1 no longer corresponds with a worksheet title?


Here is what I'm using to populate that master sheet:

Private Sub Worksheet_Activate()    Dim ws, Master As Worksheet
    Dim ColIndex As Integer
    Dim CopyRange As Range
    Set Master = ThisWorkbook.Sheets("Master")


    ColIndex = 3 'first column on Master sheet to paste data
    For Each ws In ThisWorkbook.Worksheets
        If IsNumeric(ws.Name) Then
           Master.Cells(1, ColIndex) = ws.Range("A1") 'put job name in first row
           Set CopyRange = Intersect(ws.UsedRange, ws.Range("N:N"))
       CopyRange.Copy
       Master.Cells(2, ColIndex).PasteSpecial xlValues
           ColIndex = ColIndex + 1
           Set CopyRange = Nothing
        End If
    Next ws
End Sub

I appreciate any help and explanation as I'm still learning how to refine my skills.

Thanks!