Create a summary worksheet from different workbooks (formulas created with VBA macro)

Note : Copy the code in a Standard module of your workbook, working in Mac Excel 2016 and higher.

Example 1

This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select in the browse dialog. Note: in this example you can only select xls, xlsx and xlsm files, but you can add or remove extensions in the applescript string if you want. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B.

Change the following two lines of code before you run the macro. Each workbook that is selected should contain a sheet name and data range that matches your changes.
Note: If the sheet does not exist in a selected workbook, that row will be highlighted in yellow.

Tip: if you select the first file in the dialog and hold the shift key down and select the last file all files in between are also selected.

ShName = "Sheet1" '<---- Change
Set Rng = Range("A1,D5:E5,Z10") '<---- Change

Example 2

This macro will use an existing worksheet in your workbook (I use "Sheet2" in the example). It will use one row on that sheet for every workbook that you select in the browse dialog. Note: in this example you can only select xls, xlsx and xlsm files, but you can add or remove extensions in the applescript string if you want. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B.

Change the following three lines of code before you run the macro. Each workbook that is selected should contain a sheet name and data range that matches your changes and the SummWks must exist in the destination workbook (workbook with this macro).

ShName = "Sheet1" '<---- Change
Set Rng = Range("A1,D5:E5,Z10") '<---- Change
Set SummWks = Sheets("Sheet2") '<---- Change

Every time you run the macro it will add the links below the existing formulas that already on the worksheet. If the sheet not exist in a selected workbook that row will be highlighted in green and if there are already links to a workbook with that name that row will be highlighted in blue.
Note: This macro use the function LastRow that you find below the macro.

2/5/23
Web design by Will Woodgate