Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Macros
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Purge macros | Excel Worksheet Functions | |||
Enabling macros | Excel Discussion (Misc queries) | |||
sorting with macros | Excel Discussion (Misc queries) | |||
Transferring toolbars and macros to other computers | Excel Discussion (Misc queries) | |||
The available macros list in XL; how to suppress filename from showing | Excel Discussion (Misc queries) |