Robert,
indentation of the posted code can be achieved by the CODE tags. Select your code in the post editor window, then click on the # button above the window.
I've modified the code to move and insert the hits to their corresponding sheets, and also move total mismatches to sheet "OTHER".
Code:
Sub test()
Dim wsNew As Worksheet, wsActual As Worksheet
Dim SearchString As String, FirstAddress As String
Dim c As Range, Target As Range
Set wsNew = ThisWorkbook.Worksheets("New data")
For Each wsActual In ThisWorkbook.Worksheets
If wsActual.Name <> wsNew.Name Then
SearchString = wsActual.Name & "*"
With wsNew.Range("D:D")
Set c = .Find(what:=SearchString, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Do
wsActual.Range("3:3").Insert
Set Target = wsActual.Range("3:3")
c.EntireRow.Cut Destination:=Target
Set c = .FindNext(c)
Loop Until c Is Nothing
End If
End With
End If
Next
wsNew.Range("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
wsNew.Range("D1").CurrentRegion.EntireRow.Cut
ThisWorkbook.Worksheets("OTHER").Range("3:3").Insert shift:=xlDown
End Sub I'll give you a bit of explanation on how the code works.
All lines beginning with
Dim are variable declarations. I need worksheet-type, string-type and range-type variables, so I declared those. Variable declaration is not obligatory, but very much recommended. It can save you from a lot of trouble.
Code:
Set wsNew = ThisWorkbook.Worksheets("New data") this is assigning the worksheet called "New data" to the worksheet-type-variable called
wsNew. I needed to refer to sheet "New data" several times throughout the code. It could have been done by the expression
ThisWorkbook.Worksheets("New data"), but putting that sheet into a variable, and using that variable instead was much more convenient.
Code:
For Each wsActual In ThisWorkbook.Worksheets
Next
This is a loop, looping through all worksheets in the workbook.
wsActual is the loop variable, and always it contains the current worksheet. In the loop, sheet "New data" is also having its turn. With the code lines
Code:
If wsActual.Name <> wsNew.Name Then
...
End if
sheet "New data" is excluded from further processing.
And now comes the essence.
A
SearchString is made out of the worksheet names. If sheet name is, for example, "ABC" then the search string will be "ABC*", i.e. the sheet name plus a wildcard. Then column D of sheet "New data" is scanned for the search string. It will find everything that starts with "ABC". So it doesn't matter if the serach string (which is the worksheet name, actually) is 5 or 6, or 4 or 35 characters long.
It's also important that the Find operation is done with
lookat:=xlWhole switch, that is, search is aimed at full cell values. Otherwise, searching for ABC* would find
ffgdABCdesrgh as well.
So, during the full loop:
Anything that starts with ABC goes to the ABC sheet.
Anything that starts with EFG goes to the EFG sheet.
etc., the same operation for all existing sheets, excluding "New data", but including OTHER, so anything that starts with OTHER goes to the OTHER sheet.
And, finally, everything that remains, also goes to the OTHER sheet. This operation, however, is outside the loop.
I didn't fully understood that
"Check # 1234" and "Check # 2345" part in your post, but I think it is answered above.
Jimmy
Search 
