If I record a Macro that performs a number of functions on a particular set
of data, can I then run the Macro on a different set of data without the
tesium of going into the VB editor and changing it manually?? I have tried
preselecting the new data set and running the macro, but it reverts back to
the original set in the VB code.

Any suggestions appreciated.

Check the generated VBA code. Whatever actions are recorded will be executed
when you run it again. Even if you pre-select the second time, say A1:B10,
whereas in your generated VBA, you have something like selection.Action then
it would perform the action on the selection. Actually it depends on what
you want to do. Why don't you tell us exactly what you're doing and post the
generated VBA code, and someone's bound to have a solution for you.

Mangesh



"rmellison" wrote in message
...
If I record a Macro that performs a number of functions on a particular
set
of data, can I then run the Macro on a different set of data without the
tesium of going into the VB editor and changing it manually?? I have
tried
preselecting the new data set and running the macro, but it reverts back
to
the original set in the VB code.

Any suggestions appreciated.

Hi,

You might find recording the macro with relative references helps with your
problem. When you start recording the macro, Excel should show a command bar
with two buttons on it.

The first button stops recording. The second, if you click it, turns on
relative referencing.

Relative referencing means that any adjustments you make in your macro
should happen in relation to your starting point, rather than an absolute
cell address.

Click the button again to turn relative referencing back off.

Hope this helps,

Mark


"rmellison" wrote:

If I record a Macro that performs a number of functions on a particular set
of data, can I then run the Macro on a different set of data without the
tesium of going into the VB editor and changing it manually?? I have tried
preselecting the new data set and running the macro, but it reverts back to
the original set in the VB code.

Any suggestions appreciated.

Thanks to both for your help.

Mangesh:
I am basically creating a graph for a set of data, then repeating this for
many other data sets, so rather than the do this manual I want to use a macro
to make the graphs for the selected dataset.

Mark:
I have re-recorded the macro using relative referencing, but perhaps I am
not using it properly. I select the first cell, begin recording the macro and
then turn on relative referencing. After I have selected the rest of the
data, I then turn off relative referencing and complete the remaining steps.
This does not seem to be correct, the VB code is below:

ActiveCell.Range("A1:A200,C1:D200").Select
ActiveCell.Offset(199, 2).Range("A1").Activate
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("FL_ALL_PO.xls").Range( _
"L2:L201,N2:O201"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="FL_ALL_PO.xls"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.PlotArea.Select
Selection.Height = 399
ActiveChart.Legend.Select
Selection.Left = 530
Selection.Top = 33
Selection.Width = 111
Selection.Height = 48

I imagine the first two lines are for relative referencing, where A1
represents the selected cell when I start. However, line 5 and 6 seem to
revert to absolute referencing. Any ideas where I may be going wrong???

Thanks again!



"Mark Hone" wrote:

Hi,

You might find recording the macro with relative references helps with your
problem. When you start recording the macro, Excel should show a command bar
with two buttons on it.

The first button stops recording. The second, if you click it, turns on
relative referencing.

Relative referencing means that any adjustments you make in your macro
should happen in relation to your starting point, rather than an absolute
cell address.

Click the button again to turn relative referencing back off.

Hope this helps,

Mark


"rmellison" wrote:

If I record a Macro that performs a number of functions on a particular set
of data, can I then run the Macro on a different set of data without the
tesium of going into the VB editor and changing it manually?? I have tried
preselecting the new data set and running the macro, but it reverts back to
the original set in the VB code.

Any suggestions appreciated.

Hi,

Hmm... it seems relative referencing doesn't work with chart objects. The
best thing to do is amend the code to take the range you selected.

Try this code (new lines indented an extra 4 chars):
Dim srcRng As Excel.Range

ActiveCell.Range("A1:A200,C1:D200").Select
ActiveCell.Offset(199, 2).Range("A1").Activate
Set srcRng = Selection
Charts.Add
ActiveChart.ChartType = xlLine
' ActiveChart.SetSourceData Source:=Sheets("FL_ALL_PO.xls").Range( _
"L2:L201,N2:O201"), PlotBy:=xlColumns
' replace with
ActiveChart.SetSourceData Source:=srcRng, PlotBy:=xlColumns
' ActiveChart.Location Whe=xlLocationAsObject, Name:="FL_ALL_PO.xls"
' replace with
ActiveChart.Location Whe=xlLocationAsObject,
Name:=srcRng.Parent.Name
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.PlotArea.Select
Selection.Height = 399
ActiveChart.Legend.Select
Selection.Left = 530
Selection.Top = 33
Selection.Width = 111
Selection.Height = 48
' don't forget this line
Set srcRng = Nothing

See if this works,

Mark


"rmellison" wrote:

Thanks to both for your help.

Mangesh:
I am basically creating a graph for a set of data, then repeating this for
many other data sets, so rather than the do this manual I want to use a macro
to make the graphs for the selected dataset.

Mark:
I have re-recorded the macro using relative referencing, but perhaps I am
not using it properly. I select the first cell, begin recording the macro and
then turn on relative referencing. After I have selected the rest of the
data, I then turn off relative referencing and complete the remaining steps.
This does not seem to be correct, the VB code is below:

ActiveCell.Range("A1:A200,C1:D200").Select
ActiveCell.Offset(199, 2).Range("A1").Activate
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("FL_ALL_PO.xls").Range( _
"L2:L201,N2:O201"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="FL_ALL_PO.xls"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.PlotArea.Select
Selection.Height = 399
ActiveChart.Legend.Select
Selection.Left = 530
Selection.Top = 33
Selection.Width = 111
Selection.Height = 48

I imagine the first two lines are for relative referencing, where A1
represents the selected cell when I start. However, line 5 and 6 seem to
revert to absolute referencing. Any ideas where I may be going wrong???

Thanks again!



"Mark Hone" wrote:

Hi,

You might find recording the macro with relative references helps with your
problem. When you start recording the macro, Excel should show a command bar
with two buttons on it.

The first button stops recording. The second, if you click it, turns on
relative referencing.

Relative referencing means that any adjustments you make in your macro
should happen in relation to your starting point, rather than an absolute
cell address.

Click the button again to turn relative referencing back off.

Hope this helps,

Mark


"rmellison" wrote:

If I record a Macro that performs a number of functions on a particular set
of data, can I then run the Macro on a different set of data without the
tesium of going into the VB editor and changing it manually?? I have tried
preselecting the new data set and running the macro, but it reverts back to
the original set in the VB code.

Any suggestions appreciated.

Works like a charm. Thanks for your help!

"Mark Hone" wrote:

Hi,

Hmm... it seems relative referencing doesn't work with chart objects. The
best thing to do is amend the code to take the range you selected.

Try this code (new lines indented an extra 4 chars):
Dim srcRng As Excel.Range

ActiveCell.Range("A1:A200,C1:D200").Select
ActiveCell.Offset(199, 2).Range("A1").Activate
Set srcRng = Selection
Charts.Add
ActiveChart.ChartType = xlLine
' ActiveChart.SetSourceData Source:=Sheets("FL_ALL_PO.xls").Range( _
"L2:L201,N2:O201"), PlotBy:=xlColumns
' replace with
ActiveChart.SetSourceData Source:=srcRng, PlotBy:=xlColumns
' ActiveChart.Location Whe=xlLocationAsObject, Name:="FL_ALL_PO.xls"
' replace with
ActiveChart.Location Whe=xlLocationAsObject,
Name:=srcRng.Parent.Name
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.PlotArea.Select
Selection.Height = 399
ActiveChart.Legend.Select
Selection.Left = 530
Selection.Top = 33
Selection.Width = 111
Selection.Height = 48
' don't forget this line
Set srcRng = Nothing

See if this works,

Mark


"rmellison" wrote:

Thanks to both for your help.

Mangesh:
I am basically creating a graph for a set of data, then repeating this for
many other data sets, so rather than the do this manual I want to use a macro
to make the graphs for the selected dataset.

Mark:
I have re-recorded the macro using relative referencing, but perhaps I am
not using it properly. I select the first cell, begin recording the macro and
then turn on relative referencing. After I have selected the rest of the
data, I then turn off relative referencing and complete the remaining steps.
This does not seem to be correct, the VB code is below:

ActiveCell.Range("A1:A200,C1:D200").Select
ActiveCell.Offset(199, 2).Range("A1").Activate
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("FL_ALL_PO.xls").Range( _
"L2:L201,N2:O201"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="FL_ALL_PO.xls"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.PlotArea.Select
Selection.Height = 399
ActiveChart.Legend.Select
Selection.Left = 530
Selection.Top = 33
Selection.Width = 111
Selection.Height = 48

I imagine the first two lines are for relative referencing, where A1
represents the selected cell when I start. However, line 5 and 6 seem to
revert to absolute referencing. Any ideas where I may be going wrong???

Thanks again!



"Mark Hone" wrote:

Hi,

You might find recording the macro with relative references helps with your
problem. When you start recording the macro, Excel should show a command bar
with two buttons on it.

The first button stops recording. The second, if you click it, turns on
relative referencing.

Relative referencing means that any adjustments you make in your macro
should happen in relation to your starting point, rather than an absolute
cell address.

Click the button again to turn relative referencing back off.

Hope this helps,

Mark


"rmellison" wrote:

If I record a Macro that performs a number of functions on a particular set
of data, can I then run the Macro on a different set of data without the
tesium of going into the VB editor and changing it manually?? I have tried
preselecting the new data set and running the macro, but it reverts back to
the original set in the VB code.

Any suggestions appreciated.