Duplicate ranges name

[TTThio]

Hi there,

is there a way to name 2 different ranges in different worksheets with the same name to be able to do some looping in the code?
Just like column A is named as it is in all worksheets.

I appreciate any help.

Tin Tin

 

[JVFriederick]

The only way I've succeeded in getting it to work is naming all my ranges, then creating multiple copies of the sheet, which passes the range names to the new sheets. Typically this was needed for a file with 12 sheets reflecting each month of the year. After all 12 sheets were created from a single master, I could modify each range name separately as you described. After the sheets were created I could not make the same range name refer to different ranges.

If you're trying to loop through code, maybe try naming the ranges A_NAME, B_NAME, C_NAME for various sheets. Then within your code, append A_, B_, and so on to the NAME before you use it. Numbers may even work better.

Hope this helps.

 

[TTThio]

Thanks Frederick.

I tried to copy the sheet which ranges have been named, but it didn't paste as it is in the new sheet. Is there a special way to do it?

Moreover, as I'm just a beginner, I would really appreciate if you can give me a code example for the append method.
I'm thinking about using name instead of number because in case we need to insert a column, we don't need to change the code.

Thanks again.

 

[JVFriederick]

It only works when there is 1 sheet in the file, as in, it's too late for you ! Honestly, it's not a good strategy for building a flexible file. If you ever add more sheets later - it won't work. One time shot. You can get away with it when you know ahead of time how many sheets you will have, as in months of the year.

Let me try and cook up some code.

 

[JVFriederick]

I forgot that Excel does not accept range names beginning with numbers! Here's what I came up with.

1) Name each sheet with a letter code at the front end.
A_Cost
B_Sales
C_SouthRegion
D_WestRegion and so on.

2) Now range name unique ranges on each sheet. I used A_NAME, B_NAME, etc. the last part being the same on each sheet.


The code I worked up looks something like this :

Sub QuickTest()

For Each n In ThisWorkbook.Sheets ' Loop through all sheets in workbook
With n
If n.Name = "Summary" Then GoTo SkipIt ' Do not perform procedure on sheet named Summary
n.Select ' Select each sheet in sequence
Name2Use = Left(n.Name, 2) + "NAME" ' Grab first 2 characters of sheet name and append to NAME
Range(Name2Use).Select
' Now do your thing
'
'
SkipIt:
End With
Next

Sheets(1).Select ' Go back to first sheet in Workbook file

End Sub



When you said "just a beginner", does that mean you know how to create VB code and link to macro buttons? Anyway, I'm out of time here. Good luck.

 

[TTThio]

Thanks a lot for the idea. I'll work on it, it probably will do with some modification.

Anyway, when I mean 'beginner', I do know how to work with VB, but with Excel as front-end, it will be my first time..he...

Thanks again! I really appreciate it.