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.
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".
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.
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.
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.