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

 

Comments

[Formal Dresses]

Thank for your time to share us this discuss, i have some opinion on the <a href="http://www.dressaler.com/" title="Formal Dresses">Formal Dresses</a>, I feel so stronl about your article, and learning more on this topic, then it is so good, Do you have time visit us <a href="http://www.dressaler.com/wedding-party-dress/bridesmaid-dresses.html" title="Wedding Bridesmaid Dresses">Wedding Bridesmaid Dresses</a>.I will bookmark your blog and have my kids check up here frequently <a href="http://www.dressaler.com/wedding-party-dress/junior-bridesmaid-dresses.html" title="Junior Bridesmaid Dresses">Junior Bridesmaid Dresses</a>. I’m very certain they will understand lots of new stuff here than anybody else. I do appreciate that youve added relevant and intelligent commentary here though. Thank you!