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
|
|||
|
|||
Is there a macro to create charts automatically?
Hi,
I have a sheet of data that relates to individual performance scores. Each person has their data on one row which goes - Name, Team, Score 1, Target, Score 2, Target, Score 3, Target, Score 4, Target. I need to produce an individual graph for each person containing only the 4 score results. As there are over 120 individuals, is there a way, without manually creating each one of using a macro or something to make chart creation easier? |
#2
|
|||
|
|||
Is there a macro to create charts automatically?
Select your range (including header row) and run this macro:
Sub MakeMyCharts() ' Dim ws As Worksheet Dim rng As Range Dim Yaddr As String Dim Y2addr As String Dim Xaddr As String Dim iRow As Long Dim iSrs As Long Dim iAddr As Long Dim cht As Chart Set ws = ActiveSheet If TypeName(Selection) "Range" Then MsgBox "Select the data range for the charts" Exit Sub End If Set rng = Selection For iRow = 2 To rng.Rows.Count Set cht = Charts.Add cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value For iSrs = cht.SeriesCollection.Count To 1 Step -1 cht.SeriesCollection(iSrs).Delete Next With cht.SeriesCollection.NewSeries .Name = rng.Rows(iRow).Resize(1, 2) Yaddr = "=(" Y2addr = "=(" Xaddr = "=(" For iAddr = 3 To 9 Step 2 Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Next Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")" Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")" Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")" .Values = Yaddr .XValues = Xaddr .ChartType = xlLineMarkers End With With cht.SeriesCollection.NewSeries .Name = "Target" .Values = Y2addr .ChartType = xlColumnClustered End With Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Hi, I have a sheet of data that relates to individual performance scores. Each person has their data on one row which goes - Name, Team, Score 1, Target, Score 2, Target, Score 3, Target, Score 4, Target. I need to produce an individual graph for each person containing only the 4 score results. As there are over 120 individuals, is there a way, without manually creating each one of using a macro or something to make chart creation easier? |
#3
|
|||
|
|||
Is there a macro to create charts automatically?
Thanks for this, only trouble is, when I run it, I get a Compile Error/Syntax
Error, with the following highlighted in Red; Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Regards, JDB "Jon Peltier" wrote: Select your range (including header row) and run this macro: Sub MakeMyCharts() ' Dim ws As Worksheet Dim rng As Range Dim Yaddr As String Dim Y2addr As String Dim Xaddr As String Dim iRow As Long Dim iSrs As Long Dim iAddr As Long Dim cht As Chart Set ws = ActiveSheet If TypeName(Selection) "Range" Then MsgBox "Select the data range for the charts" Exit Sub End If Set rng = Selection For iRow = 2 To rng.Rows.Count Set cht = Charts.Add cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value For iSrs = cht.SeriesCollection.Count To 1 Step -1 cht.SeriesCollection(iSrs).Delete Next With cht.SeriesCollection.NewSeries .Name = rng.Rows(iRow).Resize(1, 2) Yaddr = "=(" Y2addr = "=(" Xaddr = "=(" For iAddr = 3 To 9 Step 2 Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Next Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")" Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")" Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")" .Values = Yaddr .XValues = Xaddr .ChartType = xlLineMarkers End With With cht.SeriesCollection.NewSeries .Name = "Target" .Values = Y2addr .ChartType = xlColumnClustered End With Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Hi, I have a sheet of data that relates to individual performance scores. Each person has their data on one row which goes - Name, Team, Score 1, Target, Score 2, Target, Score 3, Target, Score 4, Target. I need to produce an individual graph for each person containing only the 4 score results. As there are over 120 individuals, is there a way, without manually creating each one of using a macro or something to make chart creation easier? |
#4
|
|||
|
|||
Is there a macro to create charts automatically?
You have to watch the line wrapping in these newsgroup posts. Each of these
is a single line of code: Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Thanks for this, only trouble is, when I run it, I get a Compile Error/Syntax Error, with the following highlighted in Red; Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Regards, JDB "Jon Peltier" wrote: Select your range (including header row) and run this macro: Sub MakeMyCharts() ' Dim ws As Worksheet Dim rng As Range Dim Yaddr As String Dim Y2addr As String Dim Xaddr As String Dim iRow As Long Dim iSrs As Long Dim iAddr As Long Dim cht As Chart Set ws = ActiveSheet If TypeName(Selection) "Range" Then MsgBox "Select the data range for the charts" Exit Sub End If Set rng = Selection For iRow = 2 To rng.Rows.Count Set cht = Charts.Add cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value For iSrs = cht.SeriesCollection.Count To 1 Step -1 cht.SeriesCollection(iSrs).Delete Next With cht.SeriesCollection.NewSeries .Name = rng.Rows(iRow).Resize(1, 2) Yaddr = "=(" Y2addr = "=(" Xaddr = "=(" For iAddr = 3 To 9 Step 2 Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Next Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")" Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")" Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")" .Values = Yaddr .XValues = Xaddr .ChartType = xlLineMarkers End With With cht.SeriesCollection.NewSeries .Name = "Target" .Values = Y2addr .ChartType = xlColumnClustered End With Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Hi, I have a sheet of data that relates to individual performance scores. Each person has their data on one row which goes - Name, Team, Score 1, Target, Score 2, Target, Score 3, Target, Score 4, Target. I need to produce an individual graph for each person containing only the 4 score results. As there are over 120 individuals, is there a way, without manually creating each one of using a macro or something to make chart creation easier? |
#5
|
|||
|
|||
Is there a macro to create charts automatically?
Many thanks for this. Works perfectly!! You've just saved me a whole heap of
time! "Jon Peltier" wrote: You have to watch the line wrapping in these newsgroup posts. Each of these is a single line of code: Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Thanks for this, only trouble is, when I run it, I get a Compile Error/Syntax Error, with the following highlighted in Red; Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Regards, JDB "Jon Peltier" wrote: Select your range (including header row) and run this macro: Sub MakeMyCharts() ' Dim ws As Worksheet Dim rng As Range Dim Yaddr As String Dim Y2addr As String Dim Xaddr As String Dim iRow As Long Dim iSrs As Long Dim iAddr As Long Dim cht As Chart Set ws = ActiveSheet If TypeName(Selection) "Range" Then MsgBox "Select the data range for the charts" Exit Sub End If Set rng = Selection For iRow = 2 To rng.Rows.Count Set cht = Charts.Add cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value For iSrs = cht.SeriesCollection.Count To 1 Step -1 cht.SeriesCollection(iSrs).Delete Next With cht.SeriesCollection.NewSeries .Name = rng.Rows(iRow).Resize(1, 2) Yaddr = "=(" Y2addr = "=(" Xaddr = "=(" For iAddr = 3 To 9 Step 2 Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Next Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")" Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")" Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")" .Values = Yaddr .XValues = Xaddr .ChartType = xlLineMarkers End With With cht.SeriesCollection.NewSeries .Name = "Target" .Values = Y2addr .ChartType = xlColumnClustered End With Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Hi, I have a sheet of data that relates to individual performance scores. Each person has their data on one row which goes - Name, Team, Score 1, Target, Score 2, Target, Score 3, Target, Score 4, Target. I need to produce an individual graph for each person containing only the 4 score results. As there are over 120 individuals, is there a way, without manually creating each one of using a macro or something to make chart creation easier? |
#6
|
|||
|
|||
Is there a macro to create charts automatically?
Further to this, I 've been asked to change the results to percentages. So
instead of the numerical score and target, I just need to show the four percentages. I've tried copying and pasting the macro, but it doesn't work. What changes do I need to make to get it working? JDB "JDB" wrote: Many thanks for this. Works perfectly!! You've just saved me a whole heap of time! "Jon Peltier" wrote: You have to watch the line wrapping in these newsgroup posts. Each of these is a single line of code: Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Thanks for this, only trouble is, when I run it, I get a Compile Error/Syntax Error, with the following highlighted in Red; Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Regards, JDB "Jon Peltier" wrote: Select your range (including header row) and run this macro: Sub MakeMyCharts() ' Dim ws As Worksheet Dim rng As Range Dim Yaddr As String Dim Y2addr As String Dim Xaddr As String Dim iRow As Long Dim iSrs As Long Dim iAddr As Long Dim cht As Chart Set ws = ActiveSheet If TypeName(Selection) "Range" Then MsgBox "Select the data range for the charts" Exit Sub End If Set rng = Selection For iRow = 2 To rng.Rows.Count Set cht = Charts.Add cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value For iSrs = cht.SeriesCollection.Count To 1 Step -1 cht.SeriesCollection(iSrs).Delete Next With cht.SeriesCollection.NewSeries .Name = rng.Rows(iRow).Resize(1, 2) Yaddr = "=(" Y2addr = "=(" Xaddr = "=(" For iAddr = 3 To 9 Step 2 Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Next Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")" Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")" Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")" .Values = Yaddr .XValues = Xaddr .ChartType = xlLineMarkers End With With cht.SeriesCollection.NewSeries .Name = "Target" .Values = Y2addr .ChartType = xlColumnClustered End With Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Hi, I have a sheet of data that relates to individual performance scores. Each person has their data on one row which goes - Name, Team, Score 1, Target, Score 2, Target, Score 3, Target, Score 4, Target. I need to produce an individual graph for each person containing only the 4 score results. As there are over 120 individuals, is there a way, without manually creating each one of using a macro or something to make chart creation easier? |
Thread Tools | |
Display Modes | |
|
|