VBA - Condition - look for a certain cell value

[juergenkemeter]

Hi,
My code looks into a folder with several xls files and opens each one of them.
Then it copies a specific range out of a sheet and gatheres it into a new sheet.

Unfortunately the range changes between the xls files.

It would be necessary to look out for the common header string 'Primary Sequences', and then select the range (cols B to M) below this, until the next header 'Derived Sequences' occurs.

If someone knows how to add such a condition to my code, this would be very helpful!

I have enclosed example files.

Sub Test_dateiensuchen_und_daten_extrahieren()
     
    Dim fs As Variant, i As Integer, bla
    Dim strRange As String, colcount As Integer, colcount2 As Integer
    Set fs = Application.FileSearch
     
    colcount = 2
    colcount2 = 5
    
    strRange = "B" & colcount & ":M5"
     
    With fs
        .LookIn = "M:\Development\GeneSheets_DataExtract_Loop\Gene.File.Lists"
        .SearchSubFolders = True 'Unterordner auch durchsuchen
        .Filename = "*.xls" 'alle Excel-Dateien
        .Execute
         
        For i = 1 To .FoundFiles.count - 1
            
            Workbooks.Open .FoundFiles(i), UpdateLinks:=False 'disable message boxes
            bla = ActiveWorkbook.Worksheets("Sequence Data").Range("B6:M9")
            ActiveWorkbook.Close savechanges:=False
            
            Range(strRange) = bla
            colcount = colcount + 4
            colcount2 = colcount2 + 4
            strRange = "B" & colcount & ":M" & colcount2
            'Range("B2:M5").Formula = bla
        Next i
         
    End With
     
     
    Set fs = Nothing
End Sub

Cheers,
Jurgen