Macros for making a graph in different tabs. Closed

Question

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.

TrowaD · Answer

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

TrowaD · Answer

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

Macros for making a graph in different tabs.

2 responses

Similar discussions

Subscribe To Our Newsletter!