I'm adding a common Named Range to every sheet, looping through all sheets. I want to apply the following.
Dim wsSheet As Worksheet
Set wsSheet = ActiveSheet
wsSheet.Names.Add Name:="NewName2", RefersTo:="='Sheet1'!$D$5"
The reference to the Sheet1 need to change on each sheet. I can see this will be a pain if I construct a string from the sheet name. Is there a more elegant way to refer to wsSheet.Name in the RefersTo ?
Or is strThisSheet = "='" + wsSheet.Name + "'!$D$5" the only way?