VBA Looping Thru Sheets, and Adding and Naming Ranges in Sheets

[Peter_W]

Hello,

I am trying to write a few lines of code that will:

- loop through all worksheets in my workbook,
- and if the worksheet name begins with "GL",
- then add and name a small range in that worksheet. And, range name will = worksheet name

I have code that partially works, but looking at the named ranges in Excel Name Manager, all the newly created ranges look like this:

GL_05684 #Ref ! =#Ref !$D$1:$D$2

So with that outcome, I did not get a correctly created range in each worksheet.

I am aware of other code to name ranges, that goes like the following, but haven't been able to totally understand how to use it with looping and variables:

Worksheets("Sheet1").Names.Add Name::="TestRange", _
RefersToR1C1:="=Sheet1!R1C1:R6C6" (So just offering this code fyi...again, I did not use this version)

Here's the code I have tried, which I would like to tweak to work correctly:

Sub NameRanges()
Dim Ws as Worksheet

For Each Ws In Worksheets
Ws.Activate

If Left(Ws.Name, 2) = "GL" Then

Range("D1:D2").Name = Ws.Name

End If

Next

End Sub

So hopefully, would like to revise those 9 lines of code. Any help, much appreciated, thanks.

 

[Fluff]

Hi & welcome to MrExcel.
That code works for me, what are your sheet names?

 

[Peter_W]

Hi & welcome to MrExcel.
That code works for me, what are your sheet names?

Fluff,

Right, the code does loop, but when I look at newly created ranges in Name Manager they all look like:


Name Value Refers To
GL_02176 #REF =#REF !$D$1:$D$D2

How do I get rid of the #REF errors?

Thanks for quick reply,
Peter_W

 

[Fluff]

What are your sheet names?

 

[Peter_W]

What are your sheet names?

Fluff,

My sheet names are: GL_0673, GL_08946, and GL_02176.

The funny thing is, the code works if I run it as a stand-alone sub. When I try to add the code to another larger sub, or call it from a smaller, separate sub, it does not work.

This has baffled me for days. There is some quirky thing I'm probably not doing right.

Thanks in advance,
Peter_W

 

[Fluff]

The code you've supplied works correctly, so it's probably something to do with the other subs.

 

[Peter_W]

This code works (or doesn't work) very inconsistently. After working a few times, I can use it again and get no ranges at all, during another looping iteration.

Totally baffles me, Pete

 

[Peter_W]

Ok Fluff, thank you for your prompt reviews. I will look at the other code I have. Peter_W

 

[Peter_W]

Fluff, I got this code to work. I had another issue that prevented it from working. Which I found and fixed. All set now. Thanks for your support!

 

[Fluff]

Glad you got it sorted & thanks for the feedback