VBA Help: Using *Insert Row* to adjust distance between random distance of Row A to cell containing "ID #"

[TransRlucent]

I'm a bit new to VBA. But, I've gotten pretty far into this project at work. However, I've come across a logic problem that I don't have a knowledge base wide enough to complete:
The problem goes like this:

I have many Work books that I need to export to a master WB one at a time (this wb then processes the information through a large array of logic gates) which then are printed off in a summary of the information (this part is already done). However, in order to make the needed training for this workbook minimal, I'd like to incorporate a macro that goes into a folder (where the wbs in need of processing will go) to which the macro then cycles through each wb one after another, processes the information, and prints each of the processed and summarized information out as separate forms.

Most of this is done. However, the part I'm stuck on is the cycling through worksheets in each workbook and adjusting the margins of the top of the ws to a max of 4 rows distance between a cell containing the text "ID #" and the top of the ws (row A).

I also only have a testing copy of my macro (the big cats told me I can't take my work home):

Code:

Sub TEST_LOOP()
Dim rng     As Range, _
    rng1    As String

Range("A1").Select
Set rng = Cells.Find(What:="ID #", After:=ActiveCell, LookIn:=xlFormulas, _
            lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
If rng = 1 Then
    rng1 = rng.Address
    Do
        rng.Offset(-1, 0).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
        Set rng = Cells.FindNext(rng)
    Loop While Not rng Is Nothing And rng.Offset(-1, 0).Address <> rng1
End If
End Sub

Again, I'm new to this so this is just a patchwork of other peoples codes that's close to what I want to do.

The worksheets I need to measure sometimes come in with 1, 2, or 3 rows between it and the row containing "ID #", so I would need a macro that adjusts that distance to 4 no matter if it is 1, 2, or 3 in the unaltered version.
I know this is an If Then command. But for some reason I can't wrap my head around how to find a distance and then tell the macro to make the distance 4.

Thank you in advance to any insight.

 

[areed1192]

So I changed your code so that it's a little simpler.

Sub InsertRows()


'Declare the Variables
Dim SearchRng As Range
Dim StrToFind As String
Dim WrkSht As Worksheet


'Loop through each worksheet in the ACTIVEWORKBOOK
For Each WrkSht In ActiveWorkbook.Worksheets


    'Activate the current worksheet
    WrkSht.Activate
    
    'Find the Particular String
    Set SearchRng = Cells.Find(What:="ID #")
                
    'If we didn't find that string don't do anything
    If Not SearchRng Is Nothing Then
       
       'Store the row number
       RowNum = SearchRng.Row
       
       'Insert four rows above the the cell that contains the string we need to find.
       For i = 1 To 4
           SearchRng.EntireRow.Insert
       Next i
       
    End If
    
Next WrkSht


End Sub

So all this code will do is loop through all the worksheets in the active workbook, find the string you specified, and then if it is found it will insert 3 rows above it. This means if it was at row 6 it would be at row 10 now.

Let me know if you have any questions