Auto create 3 scatter plots in each (of 36) sheets in a workbook

R

rodwhiteley

New Member
Joined
Jan 15, 2012
Messages
37
Hey,
VBA newbie here, sorry.

TLDR: Macro to create 3 scatter plots works OK, but only for data from the sheet it was created.
How do I change the macro to refer to the current sheet, or alternately cycle through and repeat for each of the 36 sheets in the work book?

In full:
I have some experimental data, where we collected some muscle activity in 36 different experimental conditions, for 6 muscles each time. I now want to graph these as 3 X 36 Scatter plots (each scatter showing 2 muscles). Of course I could do this manually one by one, but this seems like a process that should be automated with a macro.

I used the Macro recorder to create an XY scatter plot with 2 series, 3 times each on a worksheet, ultimately getting this to work OK, including some customisation of the axes and adding a legend that I need. (Couldn't get it to work adding custom error bars using the SD's - question for another time)

However I want to now run the Macro on each of the 36 sheets in the workbook, but the macro has hardcoded the sheet name, so running the macro in another sheet pulls the data from the original sheet I recorded the macro in (in the example below, the sheet is named '(7)' )

My guess is that I need to change these absolute references to a reference to the Active Sheet, but this is beyond me at the moment.

The 36 sheets in the workbook are named (1) (2) (3) ... (36), and the data is set up exactly the same in each sheet - I have a link to some sample data below.

Here is a copy and paste from the Macro Editor that works OK on sheet (7):
Code: 
Sub Charts()
'
' Charts Macro
' Create 3 scatter plots
'


'
    Range(Selection, Cells(ActiveCell.Row, 1)).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A2:B101").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(7)'!$A$2:$B$101")
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(7)'!$B$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(7)'!$D$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(7)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(7)'!$D$2:$D$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)
    Range("A1").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("A2:A101,F2").Select
    Range("F2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(7)'!$A$2:$A$101,'(7)'!$F$2:$F$101" _
        )
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(7)'!$F$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(7)'!$H$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(7)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(7)'!$H$2:$H$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 1
    Range("A2:A101,J2").Select
    Range("J2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(7)'!$A$2:$A$101,'(7)'!$J$2:$J$101" _
        )
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(7)'!$J$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(7)'!$L$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(7)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(7)'!$L$2:$L$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)
End Sub

Here's a link to a sample sheet from the main dataset:
https://onedrive.live.com/redir?res...576&authkey=!AKV8rKmmrYTA_I8&ithint=file,xlsx

Thanks in advance for any help,
Rod
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Code just needs a covering script. I didn't see anything where Sheet name was referenced, assuming all the information and their destinations are mirroring one another, this should work.

If not we can work out kinks, just bring back what happens :)


Code: 
Sub Charts()
'
' Charts Macro
' Create 3 scatter plots
'


'
Do Until ActiveSheet.Next Is Nothing
    Range(Selection, Cells(ActiveCell.Row, 1)).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A2:B101").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(7)'!$A$2:$B$101")
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(7)'!$B$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(7)'!$D$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(7)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(7)'!$D$2:$D$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)
    Range("A1").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("A2:A101,F2").Select
    Range("F2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(7)'!$A$2:$A$101,'(7)'!$F$2:$F$101" _
        )
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(7)'!$F$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(7)'!$H$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(7)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(7)'!$H$2:$H$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 1
    Range("A2:A101,J2").Select
    Range("J2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(7)'!$A$2:$A$101,'(7)'!$J$2:$J$101" _
        )
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(7)'!$J$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(7)'!$L$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(7)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(7)'!$L$2:$L$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)


    ActiveSheet.Next

Loop
End Sub
 
Upvote 0
Hey, thanks for the reply.
There are 17 references to the sheet in there - '(7)'
I had tossed around the idea of copying and pasting the code snippet 35 more times, then find and replacing '(7)' with '(1)' ... '(2)' ... etc but that would probably take as long as making the charts individually, and there has to be a better way...
 
Upvote 0
Do you really need all the 36x3 graphs?
Why not add a sheet (0) where you place the three scattergraphs?
De graph-datasource from a dynamic link (using INDIRECT)
Use a spin button for changing the sheet the data is extracted from.
 
Upvote 0
Easy fix then, just need to clarify something.

What does the sheet name look like exactly. Without any special characters around it at all, is it simply just 7, or is it (7)?

I'm not too familiar with Charting language in VBA but I'm guessing your sheets are literally just 1, 2, 3, 4 and the '( )' marks are part of the vba scripting.
 
Upvote 0
The sheet names are:
(1)
(2)
...
(36)
In the original post, there is a copy and paste with one of the sheets as a sample (sheet (7) )
 
Upvote 0
Give this a shot, you may need to remove the ' ' around the outsides of the parenthesis.

Code: 
ub Charts()
'
' Charts Macro
' Create 3 scatter plots
'

Dim X As Integer

'X will be your sheetname


X = 1


'

Do Until ActiveSheet.Next Is Nothing

    Worksheets((X)).Select
    
    Range(Selection, Cells(ActiveCell.Row, 1)).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A2:B101").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(X)'!$A$2:$B$101")
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(X)'!$B$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(X)'!$D$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(X)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(X)'!$D$2:$D$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)
    Range("A1").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("A2:A101,F2").Select
    Range("F2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(X)'!$A$2:$A$101,'(X)'!$F$2:$F$101" _
        )
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(X)'!$F$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(X)'!$H$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(X)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(X)'!$H$2:$H$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 1
    Range("A2:A101,J2").Select
    Range("J2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range("'(X)'!$A$2:$A$101,'(X)'!$J$2:$J$101" _
        )
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.Axes(xlValue).MaximumScale = 1
    Selection.TickLabels.NumberFormat = "0.00%"
    Selection.TickLabels.NumberFormat = "0%"
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 100
    ActiveChart.FullSeriesCollection(1).Name = "='(X)'!$J$1"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "='(X)'!$L$1"
    ActiveChart.FullSeriesCollection(2).XValues = "='(X)'!$A$2:$A$101"
    ActiveChart.FullSeriesCollection(2).Values = "='(X)'!$L$2:$L$101"
    ActiveChart.ChartTitle.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendTop)

X = X + 1

    ActiveSheet.Next

Loop
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Prevent Line from Continuing Point-to-Point (Non-continuous) On Scatter Plot in VBA
Replies
1
Views
447
Jon Peltier
J
D
  • Question
Very minor change in code had a dramatic impact on the speed. Why?
Replies
2
Views
605
deZine
D
H
  • Question
How to make VBA code graphing sheet independent
Replies
0
Views
361
Help1468
H
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
C
  • Solved
VBA macro to search each sheet cell for specific text and print to PDF when true
Replies
6
Views
569
camerong
C

Forum statistics

Threads
1,214,388
Messages
6,119,227
Members
448,878
Latest member
Da9l87

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top