VB

[xgary.wilsonx]

Good Morning

CAn someone tell me if its possible to write a macro that will ammend the code in other Macros?

I have the code below, but i want the users to be able to amend the range (highlighted in RED) ideally by entering a number on the sheet and hitting a button to activate a macro that will amend the code.

Sub Columns2()
'
' Columns2 Macro
' Macro recorded 22/12/2009 by WGS
'
'
ActiveSheet.ChartObjects("Chart 1026").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Auto").Range("A40:C69"), PlotBy:= _
xlColumns
ActiveWindow.Visible = False
Windows("graphs GW v2.xls").Activate
Range("B37").Select
End Sub

Any takers??

Many thanks in advance


Gary

 

[Andrew Poulsom]

In that code "A40:C69" is just a string, so you can use a string variable in its place. You can also build it with concatenation. Say you wanted to use the value in A1 on Sheet1 instead of the 69:

Dim LR As Long
LR = Worksheets("Sheet1").Range("A1").Value
ActiveChart.SetSourceData Source:=Sheets("Auto").Range("A40:C" & LR), PlotBy:= _
xlColumns

 

[Norie]

Gary

It is possible to write code that writes code, but it's not always a good idea.

It could cause security/compatibility/whatever issues.

Couldn't you just write the code so it takes a value from a worksheet or user input rather than trying to rewrite the code?

Something like this pure guesswork perhaps.

Sub Columns2()
    ActiveSheet.ChartObjects("Chart 1026").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Auto").Range("A40:C" & Range("A1")), PlotBy:= _
                              xlColumns
End Sub

 

[xgary.wilsonx]

Great stuff

What code would i use if i want both of the values in the range to be linked to cells within the sheet, say A1 & A2???

Many thanks


Gary

 

[Andrew Poulsom]

Try:

Dim FR As long
Dim LR As Long
FR = Worksheets("Sheet1").Range("A1").Value
LR = Worksheets("Sheet1").Range("A2").Value
ActiveChart.SetSourceData Source:=Sheets("Auto").Range("A" & FR & ":C" & LR), PlotBy:= _
xlColumns

 

[xgary.wilsonx]

Hi Andrew

Many thanks for your time on this, I have amended my code to incorporate yours and came up with the code below, however its not working debug error on the 3rd line. and ideas?

Gary


Dim FR As Long
Dim LR As Long
FR = Worksheets("graphs gw v2").Range("b35").Value
LR = Worksheets("graphs gw v2").Range("b36").Value
ActiveSheet.ChartObjects("Chart 1026").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Auto").Range("A" & FR & ":C" & LR), PlotBy:= _
xlColumns
ActiveWindow.Visible = False
Windows("graphs GW v2.xls").Activate
Range("B37").Select
End Sub

 

[xgary.wilsonx]

I think i have confussed myself and everybody who's trying to help me, sorry.

What i want to do is to be able to amend the C69 in the code below, as in change the C to a D and the 69 to 49, where D & 49 are in cells B35 and B36

COuld you possibley copy and amend my code please?

Many thanks

Gary


Sub Columns2()
'
' Columns2 Macro
' Macro recorded 22/12/2009 by WGS
'
'
ActiveSheet.ChartObjects("Chart 1026").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Auto").Range("A40:c69"), PlotBy:= _
xlColumns
ActiveWindow.Visible = False
Windows("graphs GW v2.xls").Activate
Range("B37").Select
End Sub

 

[Andrew Poulsom]

If those cells are on the active sheet try:

Sub Columns2()
'
' Columns2 Macro
' Macro recorded 22/12/2009 by WGS
'
'
    Dim LC as String
    Dim LR As Long
    LC = Range("B35").Value
    LR = Range("B36").Value
    ActiveSheet.ChartObjects("Chart 1026").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Auto").Range("A40:" & LC & LR), PlotBy:= _
        xlColumns
    ActiveWindow.Visible = False
    Windows("graphs GW v2.xls").Activate
    Range("B37").Select
End Sub

 

[xgary.wilsonx]

******** type=text/javascript> vbmenu_register("postmenu_2159909", true); *********>
Mr Poulsom you Rock!!!!

Thank you very much.

The people i work with will think i'm a god, i will tell them its thanks to you!

Gary