In which column(s) do the headers occur? Is there always only one set of
headers per file?

I would use .Find on the column containing the headers to get the relevant
start and end rows

Eg something like (untested):

'#######################
const HEADER_COL as integer=1
Dim lStart as long, lEnd as long


lStart=0:lEnd=0

with ActiveWorkbook.Worksheets("Sequence Data").columns(HEADER_COL)
on error resume next
set lStart = .Find("Primary Sequences").row
set lEnd = .Find("Primary Sequences").row
on error goto 0
end with

if lStart0 and lEnd0 then
'....calculate range to copy
end if
'######################

You might have to adjust the parameters to .Find() if you need to locate
cells based on partial content.
Try this out and post back if further questions.

Tim.

"juergenkemeter"
<juergenkemeter.21eepy_1136862001.6525@excelforu m-nospam.com wrote in
message news:juergenkemeter.21eepy_1136862001.6525@excelfo rum-nospam.com...

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.



Code:
--------------------

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.F ile.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


+-------------------------------------------------------------------+
|Filename: GeneSheets_DataExtract_Loop.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4197 |
+-------------------------------------------------------------------+

--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile:
http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=499619