Macro Modification

[Guest]

I am using this macro:

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("a10").End(xlDown).Row
LastCol = .Range("a10").End(xlToRight).Column
Set RngToName = .Range("a10", .Cells(LastRow, LastCol))
'.Names.Add Name:="'" & .Name & "'!" & NameToUse, _
RefersTo:=RngToName, Visible:=True
'or global name??
RngToName.Name = NameToUse
End With
Next sh
End Sub


The macro helps me create lookup tables. Is it possible to have the Macro
name the tables based on a list like this:

Aug1
Aug2
Aug3
Aug4
Aug5
Aug6
Aug7
Aug8

Thank you in advance.

 

[Bob Phillips]

Asuming that the list is named SheetNames, then

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
Dim i As Long

Set SheetList = ActiveWindow.SelectedSheets
For Each sh In SheetList
i = i + 1
If Application.Range("SheetNames").Cells(i, 1).Value <> "" Then
With sh
LastRow = .Range("a10").End(xlDown).Row
LastCol = .Range("a10").End(xlToRight).Column
Set RngToName = .Range("a10", .Cells(LastRow, LastCol))
RngToName.Name = Application.Range("SheetNames").Cells(i,
1).Value
End With
End If
Next sh
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thank You Bob. You have been so helpful now and in the past. Thanks Again.