Previous Topic Next Topic

Macros for making a graph in different tabs.

Closed
farru - Jun 20, 2012 at 08:10 AM
TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
 - Jun 25, 2012 at 09:46 AM
Hello,

Hey Experts,

I recently tried to use macros to make graphs. I have more than 15 tabs on my excel. Each of them have a similar data with the exact same formatting and I need to draw graphs for each of them. I tried to use macros to record the steps (ON SHEET1) but when I run the macro on other sheet (say sheet 2), it still takes the data from sheet 1. I want the graph on sheet 'x' to be plotted against the data on sheet 'x'.

Here is the code for the macros I got. I assume that changing the name of the sheet from 'sheet 1' to a dynamic value (where it reads directly) would help but I am not sure how to do it. I also tried using the 'relative reference' option but that didn't help either.

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""XYZ"""
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$A$2:$A$1041"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$E$2:$E$1041"


It would be great if any one could help.

2 replies

Answer 1 / 2
TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
 512
Jun 21, 2012 at 10:01 AM
Hi Farru,

Try this:
Sub MakeGraphs()
Dim DataSheet, GraphSheet As String
Dim x As Integer
x = 0
GraphSheet = "Graphs" 'This is the sheet's name where the graphs will be placed
DataSheet = "Sheet"
Do
x = x + 1
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=" & DataSheet & x & "!r2c1:r1041c1"
ActiveChart.SeriesCollection(1).Values = "=" & DataSheet & x & "!r2c5:r1041c5"
ActiveChart.SeriesCollection(1).Name = "=""XYZ"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=GraphSheet
Loop Until x = 15 'Graph will be created from Sheet1 - Sheet15
End Sub

After running the code you will find 15 graph's placed on top of each other on the Sheet "Graphs".

Best regards,
Trowa
0
farru
Jun 25, 2012 at 04:13 AM
Hey Trowa,

Thanks a lot for the code. The code works perfectly fine except for a few minor issues. The major problem, however remains, I am importing my data from another system and the sheets are named automatically and the naming of the sheet is not straight forward as a result, this looping system might not be very effective. IF there is an alternative, please let me know or else I could try changing the names manually. Thanks a lot Trowa.
0
Answer 2 / 2
TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
 512
Jun 25, 2012 at 09:46 AM
Hi Farru,

You can use the following code to rename the sheets automatically.
Once this code works for you (*), you can even merge the two codes together. 

Sub test()
Dim x As Integer
On Error Resume Next
x = 0
For Each Sheet In Worksheets
If Sheet.Name = "Graphs" Then GoTo NextSheet
x = x + 1
Sheet.Name = "Sheet" & x
NextSheet:
Next Sheet
End Sub

(*) When the sheets name is being changed into a name that already exists, the name won't change and the code needs to be run again. For example, if the first sheet is named ABC and the second Sheet1 then ABC cannot be renamed into Sheet1. Sheet1 will be renamed into Sheet2. Result will be first sheet ABC second sheet Sheet2. Running the code again will result in first sheet Sheet1, second sheet Sheet2. So if it occurs, now you know why.

Best regards,
Trowa
0