VBA copy rows between two specific values

[milito1990]

Hello,
I have around 100 sheets per workbook, and i'm trying to consolidate some relevant date from each Sheet to a summary one: I was wondering if there is a way to copy just selected rows into a new sheet, one below the other.
when i try to do this individually I can manage, but what i'm trying to do is copy the first Row of each Sheet (containing the ref. name of the sheet), and then a dynamic number of rows, always delimited by the same two lines of text, but not fixed in position or length (some examples of ranges A5:H9 or A15:F23 and so on). the reference text is always in a cell in column A.
I've found this code on internet that should fit my needs, but

Public Sub getAircrew()    Dim ws As Worksheet
    Dim nRow As Long
    Dim nStart As Long, nEnd As Long
    Dim range1 As Range, range2 As Range, multipleRange As Range
    
    Application.ScreenUpdating = False
    Sheets("Master").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            Set range1 = ws.Range("A1:H1")
           
            For nRow = 1 To 65536
            If Range("A" & nRow).Value = "Population Values" Then
            nStart = nRow
            Exit For
            End If
            Next nRow
            
            For nRow = nStart To 65536
            [COLOR=#ff0000]If Range("A" & nRow).Value = "* Denotes the st.dev of sample" Then[/COLOR]
            nEnd = nRow
            Exit For
            End If
            Next nRow
            nEnd = nEnd - 1


            Set range2 = ws.Range("A" & nStart & ":H" & nEnd)
            
            Set multipleRange = Union(range1, range2)
            multipleRange.Copy
            
            
            ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
        End If
    Next ws

End Sub

at the highlighted line i get a error 1004 "method range of object _global failed"
If I change the dynamic searched range with a fixed interval the code works

 

[JLGWhiz]

Try this

If Range("A" & nRow).Value [COLOR="#B22222"]Like[/COLOR] "* Denotes the st.dev of sample" Then

and if that don't work then try this

If InStr(Range("A" & nRow).Value , "Denotes the st.dev of sample") > 0 Then

 

[milito1990]

Thanks for the reply; I tried using both codes, but I get a Run-time 1004 "Application-defined or object-definederror" each time.

 

[wideboydixon]

I think you need to qualify your range lookups:

If ws.Range("A" & nRow).Value = "Population Values" Then

and

If ws.Range("A" & nRow).Value = "* Denotes the st.dev of sample" Then

WBD

 

[milito1990]

Thanks a lot! that's exactly what fixed my problem!