Update summary worksheet comment with data from worksheets

javifais

New Member
Joined
Dec 14, 2015
Messages
18
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

javifais

New Member
Joined
Dec 14, 2015
Messages
18
Hoping to get some response.
Can someone guide me on step on pasting one tiny spreadsheet as comments on a cell?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,250
Office Version
365
Platform
Windows
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

Code:
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
 

Forum statistics

Threads
1,089,259
Messages
5,407,204
Members
403,127
Latest member
zatelkac

This Week's Hot Topics

Top