Find all instances of a number, looks through all worksheets in a workbook: VBA Excel

This program uses the last worksheet in the workbook as a reference from which to find all occurences of a list of numbers in the workbook.  If it finds an occurence of the number, it will copy the entire row from the reference worksheet and paste's (replaces) the existing row containing the occurence in the 'new' found worksheet.

Sub Reconcile()

Dim xOldSht, xNewSht, xPasteSht As Worksheet

Dim acctnum As Variant                 'this is the number we are looking for in the other worksheets
Dim xOCount, xNCount As Long
Dim FindAcct, PasteFind As Range
Dim tmpName As String
Dim rowstart As Integer

rowstart = 4   'this is the row to start search on
xNCount = 0    'counts the number of occurences found. This helps us to establish if any occurences, or more than one occurence was found

Set xNewSht = Worksheets(Sheets.Count) 'this is setting  our reference sheet as the last sheet in the workbook

For xOCount = rowstart To xNewSht.Range("A65536").End(xlUp).Row  'Loop to look for all the numbers listed in the reference sheet

  For Each xOldSht In Worksheets  'loops through each sheet in the workbook
    xOldSht.Activate 'Activates the sheet in which we will search for the occurence of a number.
               
   'This is the check to see if no occurence of a number was found in any of the worksheets. If that is the case it will make the number bold in the reference worksheet.
     If xOldSht.Name = Worksheets(Sheets.Count).Name And xNCount = 0 Then
       xOldSht.Cells(xOCount, 1).Font.Bold = True
     End If
               
        acctnum = xNewSht.Cells(xOCount, 1).Value 'selects the number we will be searching for
        On Error GoTo jump: 'if no occurence of the number we are looking for is found on this sheet, an error will occur and we will skip all the following steps up to the word Jump

        Set FindAcct = Range(Cells(rowstart, 1),_
        Cells(xNewSht.Range("A65536").End(xlUp).Row, 1)). _
        Find(acctnum, LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows)
                                'sets FindAcct to the range containing the found number.
               
          If Not FindAcct Is Nothing Then 'a weird way of saying if the number was found do this
           xOldSht.Activate
           xNewSht.Cells(xOCount, 1).EntireRow.Copy_
       Destination:=FindAcct.Cells(1, 1) 'paste the row from the reference worksheet to the worksheet in which the occurence was found.
             xNCount = xNCount + 1 'Counts the found occurence
jump:
           End If
   Next xOldSht
 Next xOCount
End Sub

 

Finding and setting first row and column and last row and column: VBA Excel

Sub SetRowsColumns()
Dim nSpecificColumn, nRowFirst, nRowlast, nColFirst, nColLast as integer

    nSpecificColumn = 2

    nRowFirst = oDataSheet.Cells(1, 1).End(xlDown).Row 'Finds and sets the first used row
    
    nrowlast = oDataSheet.Cells(nRowFirst, nSpecificColumn).End(xlDown).Row 'finds and sets the last used row in a specified column
    
    nColFirst = oDataSheet.Cells(nRowFirst - 1, 1).End(xlToRight).Column 'finds and sets first used column
    
    nColLast = oDataSheet.Cells(nRowFirst - 1, nColFirst).End(xlToRight).Column 'finds and sets the last column
    
End Sub

Formatting: Remove or Display gridlines: VBA Excel

 ActiveWindow.DisplayGridlines = False

Declaring and Setting workbook and worksheet variables: VBA Excel

    Dim DataBook As Object
    Dim DataSheet As Object

    Set DataBook = ActiveWorkbook
    Set DataSheet = ActiveWorkbook.ActiveSheet

Formatting: Format cell data to date format: VBA Excel

Selection.NumberFormat = "m/d/yy"

Formatting: Autofit Columns: VBA Excel

Columns("A:I").EntireColumn.AutoFit

Count number of used Rows 2nd method: VBA Excel

    Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
    SomeIntegerVariable = Selection.Rows.Count