|
Macro for multiple charts
Hello,
I have got a little problem with a macro, that is intended to create charts on every sheet of a workbook. The macro runs and creates all the charts, but on the sheet that was active when the macro was started. I.e. there are 17 sheets in a workbook, then there are 17 charts on the first sheet after the macro was running. I really donīt know where the bug is. By the way, I am not very familiar with macros and I am working with Excel 2000. Another goody for the macro would be to select a range on each sheet, and the macro recognizes these individual ranges for the chart creation. Probably there must be a change in the code in this line: "ActiveChart.SetSourceData Source:=Sheets(vSheet).Range("C12:X145"), PlotBy:=xlColumns" but I really donīt know how this should look like. Please help me. Following the macro, as it looks currently: ---------------------------------------------------------------------------- --- Sub ChartMakro() Dim vSheet As String Num_Sheets = Sheets.Count 'Determine the number of sheets in the workbook For x = 1 To Sheets.Count 'Loop through all sheets, beginning with Sheet 2 Sheets(x).Activate 'Activate the sheet vSheet = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlLineStacked ActiveChart.SetSourceData Source:=Sheets(vSheet).Range("C12:X145"), PlotBy:=xlColumns Sheets(x).Activate ActiveChart.Location Whe=xlLocationAsObject, Name:=ActiveSheet.Name With ActiveChart Sheets(x).Activate .HasTitle = True .ChartTitle.Characters.Text = ActiveSheet.Name End With Next x 'Loop to next sheet End Sub ---------------------------------------------------------------------------- -------------- Probably my posting does not contain my realname in the "send by" -column. I donīt know how to change this in Outlook 2000. Juergen Schweizer |
Macro for multiple charts
Try this out.
Sub ChartMakro() Dim vSheet As String Num_Sheets = Sheets.Count 'Determine the number of sheets inthe Workbook For x = 1 To Sheets.Count 'Loop through all sheets,beginning with Sheet 2 Sheets (x).Activate 'Activate the sheet vSheet = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlLineStacked ActiveChart.SetSourceData Source:=Sheets(vSheet).Range ("C12:X145"), PlotBy _ :=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=Sheets(vSheet).Name With ActiveChart Sheets(x).Activate .HasTitle = True .ChartTitle.Characters.Text = ActiveSheet.Name End With Next x 'Loop to next sheet End Sub -----Original Message----- Hello, I have got a little problem with a macro, that is intended to create charts on every sheet of a workbook. The macro runs and creates all the charts, but on the sheet that was active when the macro was started. I.e. there are 17 sheets in a workbook, then there are 17 charts on the first sheet after the macro was running. I really donīt know where the bug is. By the way, I am not very familiar with macros and I am working with Excel 2000. Another goody for the macro would be to select a range on each sheet, and the macro recognizes these individual ranges for the chart creation. Probably there must be a change in the code in this line: "ActiveChart.SetSourceData Source:=Sheets(vSheet).Range ("C12:X145"), PlotBy:=xlColumns" but I really donīt know how this should look like. Please help me. Following the macro, as it looks currently: ---------------------------------------------------------- ------------------ --- Sub ChartMakro() Dim vSheet As String Num_Sheets = Sheets.Count 'Determine the number of sheets in the workbook For x = 1 To Sheets.Count 'Loop through all sheets, beginning with Sheet 2 Sheets (x).Activate 'Activate the sheet vSheet = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlLineStacked ActiveChart.SetSourceData Source:=Sheets(vSheet).Range ("C12:X145"), PlotBy:=xlColumns Sheets(x).Activate ActiveChart.Location Whe=xlLocationAsObject, Name:=ActiveSheet.Name With ActiveChart Sheets(x).Activate .HasTitle = True .ChartTitle.Characters.Text = ActiveSheet.Name End With Next x 'Loop to next sheet End Sub ---------------------------------------------------------- ------------------ -------------- Probably my posting does not contain my realname in the "send by" -column. I donīt know how to change this in Outlook 2000. Juergen Schweizer . |
Macro for multiple charts
Hi,
I tried this out, but somehow excel doesnīt like the command "(x).Activate". Maybe I misunderstood something, but the "(x).Activate" command stands at the beginning of a line, which is not accepted by excel. Juergen "DMoney" schrieb im Newsbeitrag ... Try this out. Sub ChartMakro() Dim vSheet As String Num_Sheets = Sheets.Count 'Determine the number of sheets inthe Workbook For x = 1 To Sheets.Count 'Loop through all sheets,beginning with Sheet 2 Sheets (x).Activate 'Activate the sheet vSheet = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlLineStacked ActiveChart.SetSourceData Source:=Sheets(vSheet).Range ("C12:X145"), PlotBy _ :=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=Sheets(vSheet).Name With ActiveChart Sheets(x).Activate .HasTitle = True .ChartTitle.Characters.Text = ActiveSheet.Name End With Next x 'Loop to next sheet End Sub -----Original Message----- Hello, I have got a little problem with a macro, that is intended to create charts on every sheet of a workbook. The macro runs and creates all the charts, but on the sheet that was active when the macro was started. I.e. there are 17 sheets in a workbook, then there are 17 charts on the first sheet after the macro was running. I really donīt know where the bug is. By the way, I am not very familiar with macros and I am working with Excel 2000. Another goody for the macro would be to select a range on each sheet, and the macro recognizes these individual ranges for the chart creation. Probably there must be a change in the code in this line: "ActiveChart.SetSourceData Source:=Sheets(vSheet).Range ("C12:X145"), PlotBy:=xlColumns" but I really donīt know how this should look like. Please help me. Following the macro, as it looks currently: ---------------------------------------------------------- ------------------ --- Sub ChartMakro() Dim vSheet As String Num_Sheets = Sheets.Count 'Determine the number of sheets in the workbook For x = 1 To Sheets.Count 'Loop through all sheets, beginning with Sheet 2 Sheets (x).Activate 'Activate the sheet vSheet = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlLineStacked ActiveChart.SetSourceData Source:=Sheets(vSheet).Range ("C12:X145"), PlotBy:=xlColumns Sheets(x).Activate ActiveChart.Location Whe=xlLocationAsObject, Name:=ActiveSheet.Name With ActiveChart Sheets(x).Activate .HasTitle = True .ChartTitle.Characters.Text = ActiveSheet.Name End With Next x 'Loop to next sheet End Sub ---------------------------------------------------------- ------------------ -------------- Probably my posting does not contain my realname in the "send by" -column. I donīt know how to change this in Outlook 2000. Juergen Schweizer . |
Macro for multiple charts
the post just truncated the text try this.
Sub ChartMakro() Dim vSheet As String Num_Sheets = Sheets.Count For x = 1 To Sheets.Count Sheets(x).Activate vSheet = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlLineStacked ActiveChart.SetSourceData Source:=Sheets(vSheet) _ .Range("C12:X145"), PlotBy _ :=xlColumns ActiveChart.Location Whe=xlLocationAsObject, _ Name:=Sheets(vSheet).Name With ActiveChart Sheets(x).Activate .HasTitle = True .ChartTitle.Characters.Text = ActiveSheet.Name End With Next x End Sub -----Original Message----- Hi, I tried this out, but somehow excel doesnīt like the command "(x).Activate". Maybe I misunderstood something, but the "(x).Activate" command stands at the beginning of a line, which is not accepted by excel. Juergen "DMoney" schrieb im Newsbeitrag ... Try this out. Sub ChartMakro() Dim vSheet As String Num_Sheets = Sheets.Count 'Determine the number of sheets inthe Workbook For x = 1 To Sheets.Count 'Loop through all sheets,beginning with Sheet 2 Sheets (x).Activate 'Activate the sheet vSheet = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlLineStacked ActiveChart.SetSourceData Source:=Sheets(vSheet).Range ("C12:X145"), PlotBy _ :=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=Sheets(vSheet).Name With ActiveChart Sheets(x).Activate .HasTitle = True .ChartTitle.Characters.Text = ActiveSheet.Name End With Next x 'Loop to next sheet End Sub -----Original Message----- Hello, I have got a little problem with a macro, that is intended to create charts on every sheet of a workbook. The macro runs and creates all the charts, but on the sheet that was active when the macro was started. I.e. there are 17 sheets in a workbook, then there are 17 charts on the first sheet after the macro was running. I really donīt know where the bug is. By the way, I am not very familiar with macros and I am working with Excel 2000. Another goody for the macro would be to select a range on each sheet, and the macro recognizes these individual ranges for the chart creation. Probably there must be a change in the code in this line: "ActiveChart.SetSourceData Source:=Sheets(vSheet).Range ("C12:X145"), PlotBy:=xlColumns" but I really donīt know how this should look like. Please help me. Following the macro, as it looks currently: --------------------------------------------------------- - ------------------ --- Sub ChartMakro() Dim vSheet As String Num_Sheets = Sheets.Count 'Determine the number of sheets in the workbook For x = 1 To Sheets.Count 'Loop through all sheets, beginning with Sheet 2 Sheets (x).Activate 'Activate the sheet vSheet = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlLineStacked ActiveChart.SetSourceData Source:=Sheets (vSheet).Range ("C12:X145"), PlotBy:=xlColumns Sheets(x).Activate ActiveChart.Location Whe=xlLocationAsObject, Name:=ActiveSheet.Name With ActiveChart Sheets(x).Activate .HasTitle = True .ChartTitle.Characters.Text = ActiveSheet.Name End With Next x 'Loop to next sheet End Sub --------------------------------------------------------- - ------------------ -------------- Probably my posting does not contain my realname in the "send by" -column. I donīt know how to change this in Outlook 2000. Juergen Schweizer . . |
| All times are GMT +1. The time now is 04:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com