Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to work only on selected worksheets | Excel Discussion (Misc queries) | |||
Run A Macro On Selected Worksheets | Excel Worksheet Functions | |||
Modify a Macro to Repeat for all Selected Worksheets | Excel Worksheet Functions | |||
named ranges - changing ranges with month selected | Excel Programming | |||
Help: Macro to to sum across selected visible worksheets | Excel Programming |