Named Ranges

[AV_Geek]

I have a workbook with 11 Tabs, RAW, 01, 02, 03, 04 ... 50

Each tab, 01-10, I want to name Cells A1:D4 "Group 1," cells A11:D12 "Group 2," cells A33:F45 "Stats."

1. Do the ranges have to be sheet specific, of can they be relative to the current sheet? Meaning can Group 1 be A1:D4 of the current tab, or does it have to be '01'!A1:D4, '02'!:A1:D4, '03'!A1:D4, etc.

2. If they are sheet specific only, is there a way to simply copy the selected range and apply it to another sheet? I'd like a way to auto select A1:D4 on sheet 2 and then sheet 3, and then sheet 4, rather than having to select it with my mouse 49 times.

 

[MARK858]

First of all you can't have a space in the named range i.e. Group 1 would have to be Group1 or Group_1 etc.
so for 1 you could run the code below to create the named ranges, as yes they need to be sheet specific

Sub NamedRangeAV()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "RAW" Then
            ThisWorkbook.Names.Add Name:=ws.Name & "!Group1", RefersTo:=ws.Range("A1:D4")
            ThisWorkbook.Names.Add Name:=ws.Name & "!Group2", RefersTo:=ws.Range("A11:D12")
            ThisWorkbook.Names.Add Name:=ws.Name & "!Stats", RefersTo:=ws.Range("A33:F45")
        End If
    Next
 
 
End Sub

For 2 if you mean Group1 being selected each time you activate a sheet then put the code below in the ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If Sh.Name <> "RAW" Then
        Sh.Range("Group1").Select
    End If

End Sub

 

[AV_Geek]

Nice!!! Thanks

 

[MARK858]

You're welcome