looping thru named ranges

[Greg]

Hi,

I have a bunch of pairs of named ranges, all of them consist of some
leading identifier followed by LIVE or HARD, for instance spLIVE, spHARD;
RussellLIVE, RussellHARD, etc.

How would I loop through the range collection and copy/ paste values each
LIVE range into respective HARD range?

Thank you,

 

[JE McGimpsey]

One way:

Dim nmTest As Name
Dim rDest As Range
Dim rSource As Range
Dim sTest As String
For Each nmTest In ActiveWorkbook.Names
sTest = nmTest.Name
If sTest Like "*LIVE" Then
sTest = Left(sTest, Len(sTest) - 4) & "HARD"
On Error Resume Next
Set rSource = nmTest.RefersToRange
Set rDest = ActiveWorkbook.Names(sTest).RefersToRange
On Error GoTo 0
If Not rDest Is Nothing And Not rSource Is Nothing Then _
rSource.Copy Destination:=rDest
End If
Next nmTest