Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Modification
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Modification
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) "carl" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Modification
Thank You Bob. You have been so helpful now and in the past. Thanks Again.
"Bob Phillips" wrote: 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) "carl" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I email amacro? | Excel Worksheet Functions | |||
link to combobox | Excel Discussion (Misc queries) | |||
Editing a simple macro | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Macro Modification Help | Excel Worksheet Functions |