Macro insert formula in Index sheet next to each sheet name hyperlink in column and show data

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
139
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have sheet name "Index" which in Column A (starting cell A2) contains hyperlinks (sheet name) to each sheet cell reference "A5"

I want to create a macro which shall :-

1) link and display data of each sheet cell reference "A3" in Index sheet (in column B of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index")
2) link and display data of each sheet cell reference "C9" in Index sheet (in column C of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index")
3) link and display data of each sheet cell reference "F12" in Index sheet (in column D of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index")
4) Put a formula "= max(Sheetname!D:D)" (in column E of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index")
5) Put a formula "= min(Sheetname!E:E)" (in column F of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index") and shall ignore blank cells- so as not to give 0 values

Regards,
PK
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hello,

does this work as expected?

VBA Code:
Sub index()
    Application.ScreenUpdating = False
    With Sheets("Index")
        For MY_ROWS = 2 To .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A" & MY_ROWS).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
        .Range("B" & MY_ROWS).Formula = "=" & ActiveSheet.Name & "!A3"
        .Range("C" & MY_ROWS).Formula = "=" & ActiveSheet.Name & "!C9"
        .Range("D" & MY_ROWS).Formula = "=" & ActiveSheet.Name & "!F12"
        .Range("E" & MY_ROWS).Formula = "=max(" & ActiveSheet.Name & "!D:D)"
        .Range("F" & MY_ROWS).Formula = "=min(" & ActiveSheet.Name & "!E:E)"
        Next MY_ROWS
    End With
    Application.ScreenUpdating = True
    Sheets("Index").Select
End Sub

not sure what you mean by ignore blank cells
 
Upvote 0
Hi.....It does update the formulas as mentioned. But needs update for below:-

1) Macro prompts "Update:Auto" browse window for each sheet formula update..which I have to escape by pressing "Esc" key manually to continue with macro running.

2) Gives linked data in Hyperlink format, which I would like to have in original data format(numerical).

3 request for addition of 1 more code line though...for "Link last non blank data cell in column B" in column G

Regards,
PK
 
Upvote 0
Hi sir, as I had a relook:-

1) Please ignore point 2 above when you review the code, that is working fine.
2) There are some special character worksheet names( having Hypen "-" and "&") in column A ..if that has any impact on how code is working
 
Upvote 0
Sir I tested code after deleting sheet name hyperlinks in "Index" sheet having "-" and "&" in names. The code is working perfectly..so need to handle the sheet names having "-" and "&"..that is causing the error
And request for addition of 1 more code line though...for "Link last non blank data cell in column B" in column G

Regards,
PK
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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