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?
Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.