Macros for making a graph in different tabs.
Closed
farru
-
Jun 20, 2012 at 08:10 AM
TrowaD
TrowaD
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
Related:
- Macros for making a graph in different tabs.
- Macro to create tabs based on list - Guide
- Macro to create tabs and populate from other worksheets ✓ - Forum - Excel
- Macro that will allow tab 1 's information to be copy to tab 2 ✓ - Forum - Excel
- Java update tab missing - Guide
- Macro to copy data from one sheet to another based on criteria ✓ - Forum - Excel
2 replies
TrowaD
Jun 21, 2012 at 10:01 AM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
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
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
Jun 25, 2012 at 09:46 AM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
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.
(*) 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
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
Jun 25, 2012 at 04:13 AM
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.