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.
'MODULE 5 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