Change defined name based on sheet name and loop

[cjb20]

Hi,

First post to the forum, any help would be greatly appreciated!!

Managed to get the following code to loop through the sheets on the workbook and run the code without a hitch, however........

What I really need to do is have a defined name within my summary workbook change within the code as I change the sheet I loop through.

So the two are linked

eg for sheet1 name would be "UK Eq" and the defined range would be "UK_Eq". The range would not always be as similar to the sheet.

Below is where I got to before confusing myself to no end....


Any help would be great!!!!





Dim ws As Worksheet
'Dim rName As String

For Each ws In Worksheets
Select Case ws.Name
Case "UK Eq", "UK Eq Inc", "Euro Eq"
' For Each rName In Worksheets
' Select Case rName.Name
' Case "UK_Eq", "UK_Eq_Inc"

' Codey stuff

Next ws
' Next rName