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