Named range in several worksheets

[camandab]

Hello,

Below is some code I'm trying to use that will name cell in col C based on cell value in col B. However, when I run this macro the named range is only working in 1 worksheet.

To give a little background, I've got a workbook with several sheets for each company. On each sheet col B contains the section (e.g. abc_1_1) and col C contains the amount that pertains to that section. The sections are in sequence and appear on each sheet.

Is it possible to have the same named range on multiple sheets within a workbook?

Here is my code:

Dim ddasheet As Worksheet
    Dim lastA As Long
    
    lastA = Range("A65536").End(xlUp).Row
    
    
   
    For Each ddasheet In Worksheets
            
            Range("B2:C" & lastA).CreateNames Left:=True
                        
    Next ddasheet

Any help would be greatly appreciated!

 

[ChrisM]

Interesting, in my experience I avoid the same named range on different sheets, makes things too confusing!

But, if you must...to create sheet-specific names, you must include the sheet name when you create the name, not just the cell ranges.

 

[camandab]

Ok that makes sense. Because my worksheet names will be variable, I modified to code but am now getting a run-time error. I'm not sure how to handle the variable worksheet names....

Dim ddasheet As Worksheet
    Dim tbl As Range
    Dim lastA As Long
    
    lastA = Range("A65536").End(xlUp).Offset(-1, 0).Row
    
   
    For Each ddasheet In Worksheets
           
            Set tbl = ddasheet.Range("B2:C" & lastA)
                        tbl.CreateNames Top:=False, Left:=True, Bottom:=False, Right:=False
            
            
    Next ddasheet

 

[camandab]

I think I got it. I just changed the For Each line to say:

For Each ddasheet In ActiveWorkbook.Worksheets


Thanks very much for your help!