Hey everyone I'll try and keep this short. Basically I'm trying to create a Macro that wil create a summary sheet that I can re-use multiple times and I'm having trouble referencing the cells in the other workbooks.
I've bolded the particular bit of code giving me trouble:
If x = 1 Then N = N - 1
For i = 2 To N
With Sheets("Summary Sheet")
shtName = Sheets(i).Name
'Add a hyperlink to A1 of each sheet.
.Range("B" & nRow).Hyperlinks.Add _
Anchor:=.Range("B" & nRow), Address:="#'" & _
shtName & "'!A1", TextToDisplay:=shtName
.Range("B" & nRow).HorizontalAlignment = xlLeft
.Range("A" & nRow).Value = nRow - 4
[b].Range("C" & nRow).Value = "='shtName'!$D$5"[/b]
nRow = nRow + 1
End With
If you were to click on a cell in the 'C' column I want it to read "='WhatEverThisParticularSheetsNameIs'!$D$5 . My problem is that I'm using a variable I've defined as 'shtName' instead of going through and writting in each seperate sheets name. When I include shtName in the quotes it puts it as plane text rather than using the variable.
Any help would be appreciated! Keep in mind I'm pretty bad at VBA haha.