This will run (i.e. update the 'wstConsole' tab) only for the sheets in 'varMyArray':

Code:
Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 3 'Commencement row number for data. Change to suit.
    
    Dim lngEndRow As Long
    Dim rngCell As Range
    Dim lngMyRow As Long
    Dim wstConsole As Worksheet
    Dim varMySheet As Variant, _
        varMyArray As Variant
        
    Application.ScreenUpdating = False
    
    varMyArray = Array("CONTRACTS", "USED CONTRACTS") 'These are the tabs to be processed to the 'wstConsole' tab. Change to suit.
    
    Set wstConsole = Worksheets("MASTER SHEET") 'Tab name for the data to be updated into. Change to suit.
    
    For Each varMySheet In varMyArray
    
        lngEndRow = Sheets(CStr(varMySheet)).Range("A:M").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Find the last row in ws1 from columns A to M (inclusive)
        
        For Each rngCell In Sheets(CStr(varMySheet)).Range("B" & lngStartRow & ":B" & lngEndRow)
            'If there's a reg number in column B of the current row, then...
            If Len(rngCell) > 0 Then
                On Error Resume Next
                    '...try and find if it's already exists in the 'wstConsole' sheet
                     lngMyRow = 0 'Initialise variable
                    'Note if the reg number becomes numeric, you'll need to remove the double quotation marks around rngCell
                    If InStr(CStr(wstConsole.Name), " ") > 0 Then
                        lngMyRow = Evaluate("MATCH(""" & rngCell & """  ,'" & CStr(wstConsole.Name) & "'!B:B,0)") 'Put single quotation marks around 'wstConsole' tab if there's a space (or spaces) in its name
                    Else
                        lngMyRow = Evaluate("MATCH(""" & rngCell & """  ," & CStr(wstConsole.Name) & "!B:B,0)")
                    End If
                On Error GoTo 0
                'If the reg number is already in 'wstConsole' then...
                If lngMyRow <> 0 Then
                    '...link the data from SHEET1 to the applicable row in wstConsole
                    wstConsole.Range("A" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("A" & rngCell.Row).Value
                    wstConsole.Range("B" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("B" & rngCell.Row).Value
                    wstConsole.Range("K" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("C" & rngCell.Row).Value
                    wstConsole.Range("L" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("D" & rngCell.Row).Value
                    wstConsole.Range("M" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("E" & rngCell.Row).Value
                    wstConsole.Range("E" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("F" & rngCell.Row).Value
                    wstConsole.Range("H" & lngMyRow).Formula = "=D" & lngMyRow & "-E" & lngMyRow
                'Else...
                Else
                    '...find the next available row in 'wstConsole' and then link the data from the 'varMySheet' to that row.
                    lngMyRow = wstConsole.Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 'Find the last row in ws2 from columns A to H (inclusive) and increment this by 1
                    wstConsole.Range("A" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("A" & rngCell.Row).Value
                    wstConsole.Range("B" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("B" & rngCell.Row).Value
                    wstConsole.Range("K" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("C" & rngCell.Row).Value
                    wstConsole.Range("L" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("D" & rngCell.Row).Value
                    wstConsole.Range("M" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("E" & rngCell.Row).Value
                    wstConsole.Range("E" & lngMyRow).Value = Sheets(CStr(varMySheet)).Range("F" & rngCell.Row).Value
                    wstConsole.Range("H" & lngMyRow).Formula = "=D" & lngMyRow & "-E" & lngMyRow
                End If
            End If
        Next rngCell
        
    Next varMySheet
    
    Application.ScreenUpdating = True

End Sub
Note the code goes into a standard module (the same as when you record a macro).

Robert