Macro that names a sheet
 

I am working a a macro that creates a chart and then names itself. The name
could change depending on which stat the code is being ran from. I have it
working as a single macro but I do not want to repeat all of the code so I
can save on size. I have a button on my output page that selects a range of
data on my Raw Data sheet, then it starts running the macro to create the
graph. I want to get the graph code to name the sheet "Manager_AHT_Graph" or
Manager_IR_Graph" or any of the other stats that I am trending data for.
Here is where I am with the code that works fine.
Sub Create_AHT_Graph()
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_AHT_Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Now what I want it to do is take the AHT part of the name and use
cell.offset to move to the "header" row (not actually a header since it is at
line 252) and use that name. If I am picturing it right in my head, when I
push the button to run the IR graph then it should select the range and begin
creating the chart then when it goes to name it the code should be something
like (because this pukes on itself) this:
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_" & cell.Offset(1, 0) & "_Graph"

Any ideas how I can get this to work? Or a better way of not repeating that
code for every button that I need to create a graph?

Thank you,

Macro that names a sheet
 

If you offset 1 cell down as you show, then you would be within the data
range of your graph if selection is a single cell or contiguous cells in a
single row. Since I don't know where the header is relative to the
activecell, I will use the information you provided that it is in row 252 and
assume it is in the same column as the activecell:

Sub Create_AHT_Graph()
Dim cell as Range
set cell = Cells(252,ActiveCell.column)
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_" & Trim(cell.Text) & "_Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

I will assume the code you posted is correct and you just needed help on
constructing a way to furnish an argument to Name:=

--
Regards,
Tom Ogilvy


"ElkySS" wrote:

I am working a a macro that creates a chart and then names itself. The name
could change depending on which stat the code is being ran from. I have it
working as a single macro but I do not want to repeat all of the code so I
can save on size. I have a button on my output page that selects a range of
data on my Raw Data sheet, then it starts running the macro to create the
graph. I want to get the graph code to name the sheet "Manager_AHT_Graph" or
Manager_IR_Graph" or any of the other stats that I am trending data for.
Here is where I am with the code that works fine.
Sub Create_AHT_Graph()
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_AHT_Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Now what I want it to do is take the AHT part of the name and use
cell.offset to move to the "header" row (not actually a header since it is at
line 252) and use that name. If I am picturing it right in my head, when I
push the button to run the IR graph then it should select the range and begin
creating the chart then when it goes to name it the code should be something
like (because this pukes on itself) this:
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_" & cell.Offset(1, 0) & "_Graph"

Any ideas how I can get this to work? Or a better way of not repeating that
code for every button that I need to create a graph?

Thank you,

Macro that names a sheet
 

That is great. I have never used Trim(cell.Text) I will have to go do some
looking around and figure that out some more. Thanks a lot man.

"Tom Ogilvy" wrote:

If you offset 1 cell down as you show, then you would be within the data
range of your graph if selection is a single cell or contiguous cells in a
single row. Since I don't know where the header is relative to the
activecell, I will use the information you provided that it is in row 252 and
assume it is in the same column as the activecell:

Sub Create_AHT_Graph()
Dim cell as Range
set cell = Cells(252,ActiveCell.column)
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_" & Trim(cell.Text) & "_Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

I will assume the code you posted is correct and you just needed help on
constructing a way to furnish an argument to Name:=

--
Regards,
Tom Ogilvy


"ElkySS" wrote:

I am working a a macro that creates a chart and then names itself. The name
could change depending on which stat the code is being ran from. I have it
working as a single macro but I do not want to repeat all of the code so I
can save on size. I have a button on my output page that selects a range of
data on my Raw Data sheet, then it starts running the macro to create the
graph. I want to get the graph code to name the sheet "Manager_AHT_Graph" or
Manager_IR_Graph" or any of the other stats that I am trending data for.
Here is where I am with the code that works fine.
Sub Create_AHT_Graph()
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_AHT_Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Now what I want it to do is take the AHT part of the name and use
cell.offset to move to the "header" row (not actually a header since it is at
line 252) and use that name. If I am picturing it right in my head, when I
push the button to run the IR graph then it should select the range and begin
creating the chart then when it goes to name it the code should be something
like (because this pukes on itself) this:
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_" & cell.Offset(1, 0) & "_Graph"

Any ideas how I can get this to work? Or a better way of not repeating that
code for every button that I need to create a graph?

Thank you,

Macro that names a sheet
 

Trim just removes any extraneous spaces from the front and back of the value
in row 252 in case it might have some. the Text attribute returns what the
cell displays rather than what is stored in the cell. For example, if it
were a date, then you would get 07-Aug-07 (if that was the way the cell was
formatted) rather than 39301 which is the date serial number actually
stored in the cell. (this is just an example to explain Text vice value or
using no property at all).

--
Regards,
Tom Ogilvy



"ElkySS" wrote:

That is great. I have never used Trim(cell.Text) I will have to go do some
looking around and figure that out some more. Thanks a lot man.

"Tom Ogilvy" wrote:

If you offset 1 cell down as you show, then you would be within the data
range of your graph if selection is a single cell or contiguous cells in a
single row. Since I don't know where the header is relative to the
activecell, I will use the information you provided that it is in row 252 and
assume it is in the same column as the activecell:

Sub Create_AHT_Graph()
Dim cell as Range
set cell = Cells(252,ActiveCell.column)
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_" & Trim(cell.Text) & "_Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

I will assume the code you posted is correct and you just needed help on
constructing a way to furnish an argument to Name:=

--
Regards,
Tom Ogilvy


"ElkySS" wrote:

I am working a a macro that creates a chart and then names itself. The name
could change depending on which stat the code is being ran from. I have it
working as a single macro but I do not want to repeat all of the code so I
can save on size. I have a button on my output page that selects a range of
data on my Raw Data sheet, then it starts running the macro to create the
graph. I want to get the graph code to name the sheet "Manager_AHT_Graph" or
Manager_IR_Graph" or any of the other stats that I am trending data for.
Here is where I am with the code that works fine.
Sub Create_AHT_Graph()
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_AHT_Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Now what I want it to do is take the AHT part of the name and use
cell.offset to move to the "header" row (not actually a header since it is at
line 252) and use that name. If I am picturing it right in my head, when I
push the button to run the IR graph then it should select the range and begin
creating the chart then when it goes to name it the code should be something
like (because this pukes on itself) this:
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"Manager_" & cell.Offset(1, 0) & "_Graph"

Any ideas how I can get this to work? Or a better way of not repeating that
code for every button that I need to create a graph?

Thank you,