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
85
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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
 

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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
 

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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
 

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,531
Messages
5,625,369
Members
416,098
Latest member
jpmiralrio

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
Top