Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 9
There are 1 users currently browsing forums.
|
 |
06-28-2009, 08:29 AM
|
|
Registered User
|
|
Join Date: 26 Jun 2009
Location: London
MS Office Version:Excel 2007
Posts: 5
|
|
|
creating graph from multiple worksheets
Please Register to Remove these Ads
Dear All,
I am trying to create a graph to compile data from different sheets in a master sheet. I can easily do this for one sheet; but I don't know/can't find the correct syntax to loop through the sheet names. Any help would be much appreciated - new to VB so apologies if this is a really basic problem.
currently the code looks like this..where "test1" should be a k related syntax so I can loop through sheets test1 to test5- but I have no idea what?
Sub Button3_Click()
Dim k As Integer
Sheets("Sheet1").Select
ActiveSheet.Shapes.AddChart.Select
For k = 1 To 5
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(1).XValues = "='test1'!$E:$E"
ActiveChart.SeriesCollection(1).Values = "='test1'!$F:$F"
Next k
End Sub
Many Thanks for any help or links to useful info that you may have,
inuklm
Last edited by inuklm; 07-02-2009 at 07:53 AM.
|
06-29-2009, 04:26 AM
|
 |
Forum Guru
|
|
Join Date: 10 May 2004
Location: Essex, UK
MS Office Version:2003 & 2007 sp2
Posts: 7,213
|
|
|
Re: creating graph from multiple worksheets
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
|
06-29-2009, 04:26 AM
|
|
Forum Guru
|
|
Join Date: 10 May 2004
Location: Essex, UK
MS Office Version:2003 & 2007 sp2
Posts: 7,213
|
|
|
Re: creating graph from multiple worksheets
Something like this replacing fixed text with a variable
Code:
Sub Button3_Click()
Dim k As Integer
Dim strName As String
Sheets("Sheet1").Select
ActiveSheet.Shapes.AddChart.Select
For k = 1 To 5
strName = Worksheets(k).Name
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(1).XValues = "='" & strName & "'!$E:$E"
ActiveChart.SeriesCollection(1).Values = "='" & strName & "'!$F:$F"
Next k
End Sub
|
07-01-2009, 06:43 AM
|
|
Registered User
|
|
Join Date: 26 Jun 2009
Location: London
MS Office Version:Excel 2007
Posts: 5
|
|
|
Re: creating graph from multiple worksheets
Hi Andy - sorry for that, many thanks for you're reply, I will give it a try.
Thanks,
inuklm
|
07-02-2009, 06:18 AM
|
|
Registered User
|
|
Join Date: 26 Jun 2009
Location: London
MS Office Version:Excel 2007
Posts: 5
|
|
|
Re: creating graph from multiple worksheets
Dear Andy/All,
The problem I am having now is that the macro will plot the first series, create new series for my assigned "k" but will not plot the data for the others? any thoughts would be much appreciated.
I had similar issues before; my thinking was that I hadn't set the loop up properly; but it seems this was not the case,
Thanks, inuklm
below is code I am using,
Code:
Sub Button1_Click()
Dim k As Integer
Dim strName As String
Application.ScreenUpdating = False
Sheets("Sheet4").Select
ActiveSheet.Shapes.AddChart.Select
For k = 1 To 3
strName = Worksheets(k).Name
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(1).XValues = "='" & strName & "'!$c2:$c10"
ActiveChart.SeriesCollection(1).Values = "='" & strName & "'!$d2:$d10"
Next k
Application.ScreenUpdating = True
End Sub
|
07-02-2009, 06:38 AM
|
|
Forum Guru
|
|
Join Date: 10 May 2004
Location: Essex, UK
MS Office Version:2003 & 2007 sp2
Posts: 7,213
|
|
|
Re: creating graph from multiple worksheets
It would help if you could post example workbook of your data layout
|
07-02-2009, 07:06 AM
|
|
Registered User
|
|
Join Date: 26 Jun 2009
Location: London
MS Office Version:Excel 2007
Posts: 5
|
|
|
Re: creating graph from multiple worksheets
Sure, please find attached, an example workbook. Sheets1-3 are data and Sheet4/button1 enables macro.
Thanks for your help Andy,
inuklm
|
07-02-2009, 07:11 AM
|
|
Forum Guru
|
|
Join Date: 10 May 2004
Location: Essex, UK
MS Office Version:2003 & 2007 sp2
Posts: 7,213
|
|
|
Re: creating graph from multiple worksheets
You also need to change the seriescollection index value.
Currently all data ranges are being applied to the first series.
Code:
For k = 1 To 3
strName = Worksheets(k).Name
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(k).XValues = "='" & strName & "'!$c2:$c10"
ActiveChart.SeriesCollection(k).Values = "='" & strName & "'!$d2:$d10"
Next k
This change uses the newseries object
Code:
Sub Button1_Click()
Dim k As Integer
Dim strName As String
Application.ScreenUpdating = False
Sheets("Sheet4").Select
ActiveSheet.Shapes.AddChart.Select
For k = 1 To 3
strName = Worksheets(k).Name
With ActiveChart.SeriesCollection.NewSeries
.ChartType = xlXYScatterSmoothNoMarkers
.XValues = "='" & strName & "'!$c2:$c10"
.Values = "='" & strName & "'!$d2:$d10"
End With
Next k
Application.ScreenUpdating = True
End Sub
|
07-02-2009, 07:18 AM
|
|
Registered User
|
|
Join Date: 26 Jun 2009
Location: London
MS Office Version:Excel 2007
Posts: 5
|
|
|
Re: creating graph from multiple worksheets
Ahh - got you, Many Thanks.
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
