Macro to name ranges for selected worksheets
 

I am using the macro below:

Sub maketable()

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
sh.Activate

Range("B19").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:=Application.InputBox("Enter Table Name"),
RefersToR1C1:= _
"=throttling20051222!R19C2:R97C6"

Next sh

End Sub

I need to change this part -

RefersToR1C1:= "=throttling20051222!R19C2:R97C6"

so that the new name refernences the selected worksheet.

Is it possible ?

Thank you in advance.

Macro to name ranges for selected worksheets
 

Are you trying to use the same name on all those sheets (as a sheet level name)?

If yes, maybe something like:

Option Explicit
Sub maketable2()
Dim SheetList As Sheets
Dim RngToName As Range
Dim LastRow As Long
Dim LastCol As Long
Dim NameToUse As String
Dim sh As Worksheet

NameToUse = Application.InputBox(Prompt:="Enter the Table Name")
If Trim(NameToUse) = "" Then
Exit Sub
End If

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
With sh
LastRow = .Range("B19").End(xlDown).Row
LastCol = .Range("b19").End(xlToRight).Column
Set RngToName = .Range("B19", .Cells(LastRow, LastCol))
.Names.Add Name:="'" & .Name & "'!" & NameToUse, _
RefersTo:=RngToName, Visible:=True
End With
Next sh
End Sub


carl wrote:

I am using the macro below:

Sub maketable()

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
sh.Activate

Range("B19").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:=Application.InputBox("Enter Table Name"),
RefersToR1C1:= _
"=throttling20051222!R19C2:R97C6"

Next sh

End Sub

I need to change this part -

RefersToR1C1:= "=throttling20051222!R19C2:R97C6"

so that the new name refernences the selected worksheet.

Is it possible ?

Thank you in advance.

--

Dave Peterson

Macro to name ranges for selected worksheets
 

Dave. Thank you very much for helping me.

For each sheet that has been selected (ctrl left click), I would like the
macro to prompt me to name the range. So if I have 3 selected worksheets, I
would have 3 different named ranges.

Sorry for not explaining this more clearly first time around.

"Dave Peterson" wrote:

Are you trying to use the same name on all those sheets (as a sheet level name)?

If yes, maybe something like:

Option Explicit
Sub maketable2()
Dim SheetList As Sheets
Dim RngToName As Range
Dim LastRow As Long
Dim LastCol As Long
Dim NameToUse As String
Dim sh As Worksheet

NameToUse = Application.InputBox(Prompt:="Enter the Table Name")
If Trim(NameToUse) = "" Then
Exit Sub
End If

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
With sh
LastRow = .Range("B19").End(xlDown).Row
LastCol = .Range("b19").End(xlToRight).Column
Set RngToName = .Range("B19", .Cells(LastRow, LastCol))
.Names.Add Name:="'" & .Name & "'!" & NameToUse, _
RefersTo:=RngToName, Visible:=True
End With
Next sh
End Sub


carl wrote:

I am using the macro below:

Sub maketable()

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
sh.Activate

Range("B19").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:=Application.InputBox("Enter Table Name"),
RefersToR1C1:= _
"=throttling20051222!R19C2:R97C6"

Next sh

End Sub

I need to change this part -

RefersToR1C1:= "=throttling20051222!R19C2:R97C6"

so that the new name refernences the selected worksheet.

Is it possible ?

Thank you in advance.

--

Dave Peterson

Macro to name ranges for selected worksheets
 

Option Explicit
Sub maketable2()
Dim SheetList As Sheets
Dim RngToName As Range
Dim LastRow As Long
Dim LastCol As Long
Dim NameToUse As String
Dim sh As Worksheet

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
NameToUse = Application.InputBox(Prompt:="Enter the Table Name")
If Trim(NameToUse) = "" Then
Exit Sub 'what should happen here
End If
With sh
LastRow = .Range("B19").End(xlDown).Row
LastCol = .Range("b19").End(xlToRight).Column
Set RngToName = .Range("B19", .Cells(LastRow, LastCol))
'.Names.Add Name:="'" & .Name & "'!" & NameToUse, _
RefersTo:=RngToName, Visible:=True
'or global name??
RngToName.Name = NameToUse
End With
Next sh
End Sub

There's a difference between global names and worksheet level names. This time,
I commented the worksheet/local level name code and used a global/workbook level
name.

carl wrote:

Dave. Thank you very much for helping me.

For each sheet that has been selected (ctrl left click), I would like the
macro to prompt me to name the range. So if I have 3 selected worksheets, I
would have 3 different named ranges.

Sorry for not explaining this more clearly first time around.

"Dave Peterson" wrote:

Are you trying to use the same name on all those sheets (as a sheet level name)?

If yes, maybe something like:

Option Explicit
Sub maketable2()
Dim SheetList As Sheets
Dim RngToName As Range
Dim LastRow As Long
Dim LastCol As Long
Dim NameToUse As String
Dim sh As Worksheet

NameToUse = Application.InputBox(Prompt:="Enter the Table Name")
If Trim(NameToUse) = "" Then
Exit Sub
End If

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
With sh
LastRow = .Range("B19").End(xlDown).Row
LastCol = .Range("b19").End(xlToRight).Column
Set RngToName = .Range("B19", .Cells(LastRow, LastCol))
.Names.Add Name:="'" & .Name & "'!" & NameToUse, _
RefersTo:=RngToName, Visible:=True
End With
Next sh
End Sub


carl wrote:

I am using the macro below:

Sub maketable()

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
sh.Activate

Range("B19").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:=Application.InputBox("Enter Table Name"),
RefersToR1C1:= _
"=throttling20051222!R19C2:R97C6"

Next sh

End Sub

I need to change this part -

RefersToR1C1:= "=throttling20051222!R19C2:R97C6"

so that the new name refernences the selected worksheet.

Is it possible ?

Thank you in advance.

--

Dave Peterson


--

Dave Peterson