Update summary worksheet comment with data from worksheets

[javifais]

I am working with two workbooks. In the first workbook, I have a worksheet that lists 100 ip addresses. I have another workbook that has 100 tabs. Each tab matches one of the ip addresses that are on the first workbook. Also, each tab in the second workbook has anywhere from 10 to 30 rows of data. I want to bring in the contents of each tab, after matching ip address, in column D of the first workbook, as comments.

Thanks for all your help.

 

[javifais]

Hoping to get some response.
Can someone guide me on step on pasting one tiny spreadsheet as comments on a cell?

 

[Yongle]

Suggest you test on copies of your 2 workbooks
The VBA : opens both workbooks \ loops through list of tab names \ concatenates the data in each tab \ places in column D in summary sheet
Note: apostrophe palced before last 2 lines to prevent the workbooks closing automatically whilst testing

To test
- Create a new workbook
- put this code in a module in that workbook
- amend the red values to match your details
- run

Sub From100Tabs()
    Application.ScreenUpdating = False
    Dim Wb0 As Workbook, Wb1 As Workbook, Ws0 As Worksheet, Ws1 As Worksheet
    Dim C_0 As Range, Rg0 As Range, C_1 As Range, Rg1 As Range, txt As String
    
    Set Wb0 = Workbooks.Open([COLOR=#ff0000]"C:\Test\abc\100_Tabs_Master.xlsx"[/COLOR])            'path to summary workbook
    Set Wb1 = Workbooks.Open([COLOR=#ff0000]"C:\Test\abc\100_Tabs.xlsx"[/COLOR])                   'path to workbook with 100 tabs
    Set Ws0 = Wb0.Sheets([COLOR=#ff0000]1[/COLOR])                                                 'sheet containing 100 IP addresses
    Set Rg0 = Ws0.Range([COLOR=#ff0000]"A2", Ws0.Range("A" & Ws0.Rows.Count).End(xlUp)[/COLOR])    'range containing 100 IP addresses
    
    For Each C_0 In Rg0
        txt = ""
        For Each Ws1 In Wb1.Worksheets
            If Ws1.Name = C_0 Then
                Set Rg1 = Ws1.UsedRange
                For Each C_1 In Rg1
                    If Not C_1 = "" Then txt = txt & Chr(10) & C_1
                Next C_1
                Exit For
            End If
        Next Ws1
        C_0.Offset(, 3) = txt
    Next C_0
    Wb0.Save
    [COLOR=#006400]'[/COLOR]Wb0.Close False
    [COLOR=#006400]'[/COLOR]Wb1.Close False
End Sub