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.
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.