Update summary worksheet comment with data from worksheets

javifais

New Member
Joined
Dec 14, 2015
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hoping to get some response.
Can someone guide me on step on pasting one tiny spreadsheet as comments on a cell?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top