If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
I'm trying to write a macro to create and modify charts in
spreadsheets with many worksheets. The problem I cannot solve is that the macro uses the current worksheet name (here Channel_1) in the code, as in the following short example: ' Keyboard Shortcut: Ctrl+a ' Range("G2:H11").Select Charts.Add ActiveChart.ChartType = xlXYScatterLinesNoMarkers ActiveChart.SetSourceData Source:=Sheets ("Channel_1").Range("G2:H11") ActiveChart.Location Whe=xlLocationAsObject, Name:="Channel_1" End Sub which prevents it from running in a different worksheet. I tried invented statements like .ActiveWorksheet. etc, but it didn't work... How to make the worksheet name generic to the macro without having to rename the worksheet before the macro is run? z.entropic |
#2
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
Excel doesn't have an ActiveWorksheet, but it does have an ActiveSheet.
- Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ z.entropic wrote: I'm trying to write a macro to create and modify charts in spreadsheets with many worksheets. The problem I cannot solve is that the macro uses the current worksheet name (here Channel_1) in the code, as in the following short example: ' Keyboard Shortcut: Ctrl+a ' Range("G2:H11").Select Charts.Add ActiveChart.ChartType = xlXYScatterLinesNoMarkers ActiveChart.SetSourceData Source:=Sheets ("Channel_1").Range("G2:H11") ActiveChart.Location Whe=xlLocationAsObject, Name:="Channel_1" End Sub which prevents it from running in a different worksheet. I tried invented statements like .ActiveWorksheet. etc, but it didn't work... How to make the worksheet name generic to the macro without having to rename the worksheet before the macro is run? z.entropic |
#3
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
Tried that, too--to no avail!
z.entropic -----Original Message----- Excel doesn't have an ActiveWorksheet, but it does have an ActiveSheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ z.entropic wrote: I'm trying to write a macro to create and modify charts in spreadsheets with many worksheets. The problem I cannot solve is that the macro uses the current worksheet name (here Channel_1) in the code, as in the following short example: ' Keyboard Shortcut: Ctrl+a ' Range("G2:H11").Select Charts.Add ActiveChart.ChartType = xlXYScatterLinesNoMarkers ActiveChart.SetSourceData Source:=Sheets ("Channel_1").Range("G2:H11") ActiveChart.Location Whe=xlLocationAsObject, Name:="Channel_1" End Sub which prevents it from running in a different worksheet. I tried invented statements like .ActiveWorksheet. etc, but it didn't work... How to make the worksheet name generic to the macro without having to rename the worksheet before the macro is run? z.entropic . |
#5
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
Missed that. Of course, the other solution is to use
ActiveSheet.ChartObjects.Add(L, T, W, H).Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tushar Mehta wrote: The Charts.Add creates a new chart in its own sheet. ActiveSheet at that point refers to the newly minted chartsheet! Use something like the untested: dim SrcSheet as worksheet, aChart as chart set srcsheet=activesheet set achart=charts.add achart.SetSourceData Source:=srcsheet.Range("G2:H11") .... |
#6
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
Now you both lost me--how do I use your code if my recorded macro is full of references to the specific name of the renamed active worksheet:
' Keyboard Shortcut: Ctrl+a ' Range("H2:H10").Select Charts.Add ActiveChart.ChartType = xlXYScatterLinesNoMarkers ActiveChart.SetSourceData Source:=Sheets("Data").Range("H2:H10"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9" ActiveChart.SeriesCollection(1).Name = "=Data!R1C9" ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10" ActiveChart.SeriesCollection(2).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection(2).Name = "=Data!R1C10" ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart z.entropic "Jon Peltier" wrote: Missed that. Of course, the other solution is to use ActiveSheet.ChartObjects.Add(L, T, W, H).Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tushar Mehta wrote: The Charts.Add creates a new chart in its own sheet. ActiveSheet at that point refers to the newly minted chartsheet! Use something like the untested: dim SrcSheet as worksheet, aChart as chart set srcsheet=activesheet set achart=charts.add achart.SetSourceData Source:=srcsheet.Range("G2:H11") .... |
#7
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
I gave you a specific example of how to work around the hardcoded
names. Where are you lost? Three additional points. First, while the XL macro recorder sets the charttype first and the sourcedata second, you need to reverse those steps in your macro. Second, to get strings for the Values and XValues properties, use something like ....Values= "='" & SrcSheet.name & "'!R2C8:R1000C8" Third, to create a chart that refers to only those cells that contain data, use something like: with SrcSheet ....Values= "='" & .name & "'!" _ & .range(.cells(2,8),.cells(2,8).end(xldown))) _ .address(false, false, xlr1c1) Alternatively, use named formulas. See the Dynamic Charts page of my web site for more on this methodology. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Now you both lost me--how do I use your code if my recorded macro is full of references to the specific name of the renamed active worksheet: ' Keyboard Shortcut: Ctrl+a ' Range("H2:H10").Select Charts.Add ActiveChart.ChartType = xlXYScatterLinesNoMarkers ActiveChart.SetSourceData Source:=Sheets("Data").Range("H2:H10"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9" ActiveChart.SeriesCollection(1).Name = "=Data!R1C9" ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10" ActiveChart.SeriesCollection(2).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection(2).Name = "=Data!R1C10" ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart z.entropic "Jon Peltier" wrote: Missed that. Of course, the other solution is to use ActiveSheet.ChartObjects.Add(L, T, W, H).Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tushar Mehta wrote: The Charts.Add creates a new chart in its own sheet. ActiveSheet at that point refers to the newly minted chartsheet! Use something like the untested: dim SrcSheet as worksheet, aChart as chart set srcsheet=activesheet set achart=charts.add achart.SetSourceData Source:=srcsheet.Range("G2:H11") .... |
#8
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
OK, let me explain mine and others' problem: your answer is it's like getting a book in a foreign language with one sentence translated... For you guys fluent in VB, mine and others questions may seem ridiculous and we may seem dumb, but since I see a macro code once in a quarter and don't know any of these German-looking VB statements with 5 terms joined together, I get stumped by incomplete answers. You yourself quickly added three additional points to your original reply...
I and, I trust, many other readers/posters do appreciate your time and effort in helping us guide through the VBA jungle, but many of your and others' off-side comments convince me it would be a waste of my time to try to learn VBA for the few things I need it. In the end, I have to rely on your and others' goodwill, time and effort which, hopefully, is a free ad for your business when more serious problems must be solved. Finally, both Jon's and your proposed solutions to my original problem might seem trivial to you, pros, but after trying many versions of the modified macro, I still don't know how to write/create a simple chart-creation macro working in a worksheet with an arbitrary name. So, here is a simples chart-making macro--how should it be modified using your or Jon's ideas to make it independent of the "Sheet1" name? Regards and thanks, z.entropic "Tushar Mehta" wrote: I gave you a specific example of how to work around the hardcoded names. Where are you lost? Three additional points. First, while the XL macro recorder sets the charttype first and the sourcedata second, you need to reverse those steps in your macro. Second, to get strings for the Values and XValues properties, use something like ....Values= "='" & SrcSheet.name & "'!R2C8:R1000C8" Third, to create a chart that refers to only those cells that contain data, use something like: with SrcSheet ....Values= "='" & .name & "'!" _ & .range(.cells(2,8),.cells(2,8).end(xldown))) _ .address(false, false, xlr1c1) Alternatively, use named formulas. See the Dynamic Charts page of my web site for more on this methodology. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Now you both lost me--how do I use your code if my recorded macro is full of references to the specific name of the renamed active worksheet: ' Keyboard Shortcut: Ctrl+a ' Range("H2:H10").Select Charts.Add ActiveChart.ChartType = xlXYScatterLinesNoMarkers ActiveChart.SetSourceData Source:=Sheets("Data").Range("H2:H10"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9" ActiveChart.SeriesCollection(1).Name = "=Data!R1C9" ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10" ActiveChart.SeriesCollection(2).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection(2).Name = "=Data!R1C10" ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart z.entropic "Jon Peltier" wrote: Missed that. Of course, the other solution is to use ActiveSheet.ChartObjects.Add(L, T, W, H).Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tushar Mehta wrote: The Charts.Add creates a new chart in its own sheet. ActiveSheet at that point refers to the newly minted chartsheet! Use something like the untested: dim SrcSheet as worksheet, aChart as chart set srcsheet=activesheet set achart=charts.add achart.SetSourceData Source:=srcsheet.Range("G2:H11") .... |
#9
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
OK, let me explain mine and others' problem: your answer is it's like getting a book in a foreign language with one sentence translated... For you guys fluent in VB, mine and others questions may seem ridiculous and we may seem dumb, but since I see a macro code once in a quarter and don't know any of these German-looking VB statements with 5 terms joined together, I get stumped by incomplete answers. You yourself quickly added three additional points to your original reply...
I and, I trust, many other readers/posters do appreciate your time and effort in helping us guide through the VBA jungle, but many of your and others' off-side comments convince me it would be a waste of my time to try to learn VBA for the few things I need it. In the end, I have to rely on your and others' goodwill, time and effort which, hopefully, is a free ad for your business when more serious problems must be solved. Finally, both Jon's and your proposed solutions to my original problem might seem trivial to you, pros, but after trying many versions of the modified macro, I still don't know how to write/create a simple chart-creation macro working in a worksheet with an arbitrary name. So, here is a simples chart-making macro--how should it be modified using your or Jon's ideas to make it independent of the "Sheet1" name? Regards and thanks, z.entropic "Tushar Mehta" wrote: I gave you a specific example of how to work around the hardcoded names. Where are you lost? Three additional points. First, while the XL macro recorder sets the charttype first and the sourcedata second, you need to reverse those steps in your macro. Second, to get strings for the Values and XValues properties, use something like ....Values= "='" & SrcSheet.name & "'!R2C8:R1000C8" Third, to create a chart that refers to only those cells that contain data, use something like: with SrcSheet ....Values= "='" & .name & "'!" _ & .range(.cells(2,8),.cells(2,8).end(xldown))) _ .address(false, false, xlr1c1) Alternatively, use named formulas. See the Dynamic Charts page of my web site for more on this methodology. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Now you both lost me--how do I use your code if my recorded macro is full of references to the specific name of the renamed active worksheet: ' Keyboard Shortcut: Ctrl+a ' Range("H2:H10").Select Charts.Add ActiveChart.ChartType = xlXYScatterLinesNoMarkers ActiveChart.SetSourceData Source:=Sheets("Data").Range("H2:H10"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9" ActiveChart.SeriesCollection(1).Name = "=Data!R1C9" ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10" ActiveChart.SeriesCollection(2).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection(2).Name = "=Data!R1C10" ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart z.entropic "Jon Peltier" wrote: Missed that. Of course, the other solution is to use ActiveSheet.ChartObjects.Add(L, T, W, H).Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tushar Mehta wrote: The Charts.Add creates a new chart in its own sheet. ActiveSheet at that point refers to the newly minted chartsheet! Use something like the untested: dim SrcSheet as worksheet, aChart as chart set srcsheet=activesheet set achart=charts.add achart.SetSourceData Source:=srcsheet.Range("G2:H11") .... |
#10
|
|||
|
|||
macro to create charts in worksheets with arbitrary names
Sorry, Z, it wasn't so long ago I struggled with this stuff, I should
try to remember what it was like. I'll start with the code you posted halfway up this thread. We'll define variables for the sheet name and the range. The range you selected before running the wizard in the macro recorder has no relation to the ranges used later, so we can leave it out. Your Y values for both series are in column H, and the X values are in columns I and J. Series names are in row 1, data in rows 2 through 1000. Sub MakeAChart() '' declare variables for active sheet name and selected range Dim sSheet As String Dim rRange As Range sSheet = ActiveSheet.Name '' add chart and put it where you want it Charts.Add ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet '' Add first series (sometimes charts.add does, sometimes not) If ActiveChart.SeriesCollection.Count = 0 Then ActiveChart.SeriesCollection.NewSeries End If '' Define the data and type ActiveChart.SeriesCollection(1).Values = _ "=" & sSheet & "!R2C8:R1000C8" ActiveChart.SeriesCollection(1).XValues = _ "=" & sSheet & "!R2C9:R1000C9" ActiveChart.SeriesCollection(1).Name = "=" & sSheet & "!R1C9" ActiveChart.ChartType = xlXYScatterLinesNoMarkers '' Add second series and define data ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(2).Values = _ "=" & sSheet & "!R2C8:R1000C8" ActiveChart.SeriesCollection(2).XValues = _ "=" & sSheet & "!R2C10:R1000C10" ActiveChart.SeriesCollection(2).Name = "=" & sSheet & "!R1C10" End Sub This procedure was tested and works just fine. I might make additional adjustments, but I follow the engineer's creed: If it works, you haven't messed with it enough. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ z.entropic wrote: OK, let me explain mine and others' problem: your answer is it's like getting a book in a foreign language with one sentence translated... For you guys fluent in VB, mine and others questions may seem ridiculous and we may seem dumb, but since I see a macro code once in a quarter and don't know any of these German-looking VB statements with 5 terms joined together, I get stumped by incomplete answers. You yourself quickly added three additional points to your original reply... I and, I trust, many other readers/posters do appreciate your time and effort in helping us guide through the VBA jungle, but many of your and others' off-side comments convince me it would be a waste of my time to try to learn VBA for the few things I need it. In the end, I have to rely on your and others' goodwill, time and effort which, hopefully, is a free ad for your business when more serious problems must be solved. Finally, both Jon's and your proposed solutions to my original problem might seem trivial to you, pros, but after trying many versions of the modified macro, I still don't know how to write/create a simple chart-creation macro working in a worksheet with an arbitrary name. So, here is a simples chart-making macro--how should it be modified using your or Jon's ideas to make it independent of the "Sheet1" name? Regards and thanks, z.entropic "Tushar Mehta" wrote: I gave you a specific example of how to work around the hardcoded names. Where are you lost? Three additional points. First, while the XL macro recorder sets the charttype first and the sourcedata second, you need to reverse those steps in your macro. Second, to get strings for the Values and XValues properties, use something like ....Values= "='" & SrcSheet.name & "'!R2C8:R1000C8" Third, to create a chart that refers to only those cells that contain data, use something like: with SrcSheet ....Values= "='" & .name & "'!" _ & .range(.cells(2,8),.cells(2,8).end(xldown))) _ .address(false, false, xlr1c1) Alternatively, use named formulas. See the Dynamic Charts page of my web site for more on this methodology. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Now you both lost me--how do I use your code if my recorded macro is full of references to the specific name of the renamed active worksheet: ' Keyboard Shortcut: Ctrl+a ' Range("H2:H10").Select Charts.Add ActiveChart.ChartType = xlXYScatterLinesNoMarkers ActiveChart.SetSourceData Source:=Sheets("Data").Range("H2:H10"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9" ActiveChart.SeriesCollection(1).Name = "=Data!R1C9" ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10" ActiveChart.SeriesCollection(2).Values = "=Data!R2C8:R1000C8" ActiveChart.SeriesCollection(2).Name = "=Data!R1C10" ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With ActiveChart z.entropic "Jon Peltier" wrote: Missed that. Of course, the other solution is to use ActiveSheet.ChartObjects.Add(L, T, W, H).Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tushar Mehta wrote: The Charts.Add creates a new chart in its own sheet. ActiveSheet at that point refers to the newly minted chartsheet! Use something like the untested: dim SrcSheet as worksheet, aChart as chart set srcsheet=activesheet set achart=charts.add achart.SetSourceData Source:=srcsheet.Range("G2:H11") .... |
Thread Tools | |
Display Modes | |
|
|