VBA to create a named range

R

robprager

New Member
Joined
Oct 25, 2014
Messages
8
Hi everyone, I'm a VBA newbie and trying to learn more :)

I'm running Windows 7 and Office 2013.

I read this page, http://www.mrexcel.com/forum/excel-...l-basic-applications-define-named-ranges.html, but the code that was provided isn't exactly the same as what I am after.

I have the following scenario on a worksheet (see below), except in my specific case, I have about 70 different combinations of named ranges to create, so I have populated until row 70. I'm looking for a macro that will create a named range by taking the text string in A1, and making that the named range for the range reference in B1 etc.

AB
1NamedRange1Sheet1:Sheet3!$A$1:$A$5
2NamedRange2Sheet1:Sheet3!$A$6:$A$10
3NamedRange3Sheet1:Sheet3!$A$11:$A$15

<tbody>
</tbody>

Thanks in advance to anyone who can help me out :)
 
Last edited:
Sort by date Sort by votes
Hi Domenic

I've tried to amend the code as I need two macros for slightly different purposes, producing a different range. For the first one, EG Sheet1:Sheet10 I actually want it to return in the list Sheet 1 and Sheet 10. I don't for the second one. I also don't want it to create a new sheet.

They are working fine, but when I come to exit excel after running the 2 macros from 2 buttons, it crashes. Could it be due to a memory leak?

Thanks!

Sub ListSheetNames()

Dim aSheetNames() As String
Dim sFirstSheet As String
Dim sLastSheet As String
Dim SheetCount As Long
Dim i As Long

sFirstSheet = "Sheet1" 'change the name of the first sheet accordingly
sLastSheet = "Sheet10" 'change the name of the last sheet accordingly

ReDim aSheetNames(1 To Sheets.Count)

SheetCount = 0
For i = Sheets(sFirstSheet).Index + 0 To Sheets(sLastSheet).Index - 0
SheetCount = SheetCount + 1
aSheetNames(SheetCount) = Sheets(i).Name
Next i

ReDim Preserve aSheetNames(1 To SheetCount)


Range("B45").Resize(UBound(aSheetNames)).Value = Application.Transpose(aSheetNames)

End Sub


---


Sub ListSheetNames1()


Dim aSheetNames() As String
Dim sFirstSheet As String
Dim sLastSheet As String
Dim SheetCount As Long
Dim i As Long

sFirstSheet = "BD" 'change the name of the first sheet accordingly
sLastSheet = "Ref" 'change the name of the last sheet accordingly

ReDim aSheetNames(1 To Sheets.Count)

SheetCount = 0
For i = Sheets(sFirstSheet).Index + 2 To Sheets(sLastSheet).Index - 1
SheetCount = SheetCount + 1
aSheetNames(SheetCount) = Sheets(i).Name
Next i

ReDim Preserve aSheetNames(1 To SheetCount)


Range("D45").Resize(UBound(aSheetNames)).Value = Application.Transpose(aSheetNames)

End Sub







Domenic said:
Maybe something like this...

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] ListSheetNames()

    [COLOR=darkblue]Dim[/COLOR] aSheetNames()           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFirstSheet             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sLastSheet              [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] SheetCount              [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i                       [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    sFirstSheet = "Sheet5" [COLOR=green]'change the name of the first sheet accordingly[/COLOR]
    sLastSheet = "Sheet12" [COLOR=green]'change the name of the last sheet accordingly[/COLOR]
    
    [COLOR=darkblue]ReDim[/COLOR] aSheetNames(1 [COLOR=darkblue]To[/COLOR] Sheets.Count)
    
    SheetCount = 0
    [COLOR=darkblue]For[/COLOR] i = Sheets(sFirstSheet).Index + 1 [COLOR=darkblue]To[/COLOR] Sheets(sLastSheet).Index - 1
        SheetCount = SheetCount + 1
        aSheetNames(SheetCount) = Sheets(i).Name
    [COLOR=darkblue]Next[/COLOR] i
    
    [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aSheetNames(1 [COLOR=darkblue]To[/COLOR] SheetCount)
    
    Worksheets.Add before:=Sheets(1)
    
    Range("A2").Resize(UBound(aSheetNames)).Value = Application.Transpose(aSheetNames)
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!
Click to expand...
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You didn't say where you want your list. I'll assume that you want it in the active worksheet. Accordingly, try...

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] ExclusiveList()

    [COLOR=darkblue]Dim[/COLOR] aSheetNames()           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFirstSheet             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sLastSheet              [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] SheetCount              [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i                       [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] TypeName(ActiveSheet) <> "Worksheet" [COLOR=darkblue]Then[/COLOR]
        MsgBox "Please make sure that a worksheet is the active sheet!", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    sFirstSheet = "BD" [COLOR=green]'change the name of the first sheet accordingly[/COLOR]
    sLastSheet = "Ref" [COLOR=green]'change the name of the last sheet accordingly[/COLOR]
    
    [COLOR=darkblue]ReDim[/COLOR] aSheetNames(1 [COLOR=darkblue]To[/COLOR] Sheets.Count)
    
    SheetCount = 0
    [COLOR=darkblue]For[/COLOR] i = Sheets(sFirstSheet).Index + 1 [COLOR=darkblue]To[/COLOR] Sheets(sLastSheet).Index - 1
        SheetCount = SheetCount + 1
        aSheetNames(SheetCount) = Sheets(i).Name
    [COLOR=darkblue]Next[/COLOR] i
    
    [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aSheetNames(1 [COLOR=darkblue]To[/COLOR] SheetCount)
    
    Range("D45").Resize(UBound(aSheetNames)).Value = Application.Transpose(aSheetNames)
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


Code: 
[COLOR=darkblue]Sub[/COLOR] InclusiveList()

    [COLOR=darkblue]Dim[/COLOR] aSheetNames()           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFirstSheet             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sLastSheet              [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] SheetCount              [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i                       [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] TypeName(ActiveSheet) <> "Worksheet" [COLOR=darkblue]Then[/COLOR]
        MsgBox "Please make sure that a worksheet is the active sheet!", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    sFirstSheet = "Sheet1" [COLOR=green]'change the name of the first sheet accordingly[/COLOR]
    sLastSheet = "Sheet10" [COLOR=green]'change the name of the last sheet accordingly[/COLOR]
    
    [COLOR=darkblue]ReDim[/COLOR] aSheetNames(1 [COLOR=darkblue]To[/COLOR] Sheets.Count)
    
    SheetCount = 0
    [COLOR=darkblue]For[/COLOR] i = Sheets(sFirstSheet).Index [COLOR=darkblue]To[/COLOR] Sheets(sLastSheet).Index
        SheetCount = SheetCount + 1
        aSheetNames(SheetCount) = Sheets(i).Name
    [COLOR=darkblue]Next[/COLOR] i
    
    Range("B45").Resize(UBound(aSheetNames)).Value = Application.Transpose(aSheetNames)
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
VBA to create multiple named ranges
Replies
1
Views
88
Trebor76
T
S
  • Question
VBA: All Sheets data to Master Sheet (excluding some sheet)
2
Replies
11
Views
402
kevin9999
K
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
254
birdman15
B
R
  • Question
Creating a loop to rename worksheets
Replies
4
Views
432
Micron
M
E
  • Solved
Help deleting rows and creating range name via VBA
Replies
2
Views
121
EssKayKay
E

Forum statistics

Threads
1,215,212
Messages
6,123,655
Members
449,113
Latest member
Hochanz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top