Loop Sheets with VB to copy information from all worksheets to on overview sheet

Hi Community,

thank you already now for even reading about my challenge!!

I have the following challenge:
I will have many (more than 100) sheets in my Excel file that all have the same fixed layout. Basically containing contact information like Name, Address, Phone and some additional data and notes. Each Sheetname schould be the contacts person name.
At the first sheet I would like to have an overview list that lists some of these information. JUMP should be a Hyperlink to click that brings you to the corresponding Sheet.

Overview sheet:
Name1, Phone1, Datum1, JUMP
Name2, Phone2, Datum2, JUMP
...

My first idea was of course just to do it manually
Overview sheet:
=Sheetname1!A1, =Sheetname1!A2, =Sheetname1!A3
=Sheetname2!A1, =Sheetname2!A2, =Sheetname2!A3

The problem with that solution is that the Sheetname will sometimes change and therefore all links will not function anymore. Also the number of sheets will grow over time.

I think the better solution would be to fill out the overview sheet automatically.
I think it should run this way:
- Loop all sheets
- For each sheet copy the cell A1, A2, A3 into one column at Overview Sheet
- Write JUMP in the A4 Overview Sheet and make a JUMP as a hyperlink to the current sheet in the loop (this is the biggest challenge I think!)
- Jump to next sheet
- Jump to next column

Unfortunately my VB skills don't allow me to make it happen :-(
Maybe one of you is smart enough to put that into a code? That would be really awesome!

Thank you so much for already for reading about my problem!

you could actually do this with formulas but your right its probably easier in VBA

suggest you create a small sample file with 4-5 sheets
Remember to desensitize the data.

dont click on the attachment icon

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

made me wait a whole 60 seconds (twice) to download that file

anyways, here you got
I've marked up every row with comments so you can see what does what

Sub Update_Overview()

    Dim ws1 As Worksheet 'variable for looping
    Dim ws2 As Worksheet 'set overview worksheet
    
    Dim lRow As Long 'counter to see which row overview is up to
    
    'set overview worksheet
    Set ws2 = Worksheets("Overview")
    
    'clear all previous data on overview
    ws2.Range("A2:D" & Cells.SpecialCells(xlCellTypeLastCell).Row).Clear
        
    lRow = 2 'set last row
    
    'loop every worksheet within workbook
    For Each ws1 In Worksheets
    
        If ws1.Name <> "Overview" Then 'Skip overview sheet
            ws2.Cells(lRow, 1) = ws1.Range("B1") 'Name
            ws2.Cells(lRow, 2) = Format(ws1.Range("B3"), "MM-DD-YYYY") 'date, change format as required i dont know which country you are from
            ws2.Cells(lRow, 3) = ws1.Range("B5") 'Email
            ws2.Hyperlinks.Add Anchor:=ws2.Cells(lRow, 4), Address:="", SubAddress:="'" & ws1.Name & "'!A1", TextToDisplay:="JUMP" 'insert Hyperlink
            lRow = lRow + 1 'increment lRow
        End If
    Next
    

End Sub

You do need to attach files here in future - some (many?) forum members will not follow file-sharing links out of security concerns.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

sure thing not a problem
glad to be of assistance


in the future as Ali mentioned
please use attachment feature of the site
your more likely to get responses