VBA looping through tabs, columns, and keyword searches

[nicoletta99]

Hello all! I'm hoping this is a simple one for you all… fingers crossed!

I have a spreadsheet with heaps of tabs and have two macro requirements.
1) If I list 10 tabs in cell A2-A11 of the "Collation" tab, I need a macro that will loop through each of the 10 tabs listed and copy cells A4:C40 and paste it into the "Collation" tab below each other (starting in cell A21).
2) If I have a keyword in cell B1 of the "Search" tab, I need a macro to loop through column A of every tab in the file (until an empty cell - not whole column). If it finds the keyword in any of those tabs, I need it to list what tab it found the true value (could be more than 1) in a list on the Collation tab starting in cell A4.
I'm still learning ranges and strings, and as much as I try to google examples and put it together I'm getting so many errors I don't know how to fix!
I'm hoping these examples will be the basis for many awesome spreadsheets to come - so thanks for your help

 

[hnsd24_CN]

no test

Sub test1()
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim i As Integer
    Dim r As Integer
    Set ws1 = Worksheets("Collation")
    For i = 2 To 14
        Set ws = Worksheets(ws1.Range("A" & i))
        ws.Range("A4:C40").Copy ws1.Range("A" & 21 + r)
        r = r + 37
    Next
End Sub

put result on the Collation tab starting in cell B4

Sub test2()
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Integer
    Dim s As Integer
    Dim arr(1 To 10000)
    Set ws1 = Worksheets("Collation")
    Set ws2 = Worksheets("Search")
    s = 1
    For Each ws In Worksheets
        If ws.Name <> "Collation" Or ws.Name <> "Search" Then
            i = 1
            Do Until ws.Range("A" & i) = ""
                If ws.Range("A" & i) = ws2.Range("B1") Then
                    arr(s) = ws.Name
                    s = s + 1
                    Exit Do
                End If
                i = i + 1
            Loop
        End If
    Next
    ws1.Range("B4:B" & s + 2) = Application.Transpose(arr)
End Sub

 

[nicoletta99]

no test

Sub test1()
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim i As Integer
    Dim r As Integer
    Set ws1 = Worksheets("Collation")
    For i = 2 To 14
        Set ws = Worksheets(ws1.Range("A" & i))
        ws.Range("A4:C40").Copy ws1.Range("A" & 21 + r)
        r = r + 37
    Next
End Sub

put result on the Collation tab starting in cell B4

Sub test2()
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Integer
    Dim s As Integer
    Dim arr(1 To 10000)
    Set ws1 = Worksheets("Collation")
    Set ws2 = Worksheets("Search")
    s = 1
    For Each ws In Worksheets
        If ws.Name <> "Collation" Or ws.Name <> "Search" Then
            i = 1
            Do Until ws.Range("A" & i) = ""
                If ws.Range("A" & i) = ws2.Range("B1") Then
                    arr(s) = ws.Name
                    s = s + 1
                    Exit Do
                End If
                i = i + 1
            Loop
        End If
    Next
    ws1.Range("B4:B" & s + 2) = Application.Transpose(arr)
End Sub

Thank you so much for your reply!

I've finally gotten around to having another go - but am finding that I'm getting a type mismatch?

 

[mohadin]

Replace with

 Set ws = Worksheets(ws1.Range("A" & i).Value)