Creating Multiple Graphs

B

bibalong

New Member
Joined
Mar 4, 2011
Messages
8
(Barely any experience with VBA, Probably a really dumb question with really obvious answer):confused::confused:

Hey guys, I am trying to create a macros that will enable me to create 13 graphs each using a single column of data, each on a single different sheet from my original table of files.

I have tried to use the Macros recorder as a simple way of mimicking my manual process of doing this however when I try to run the macros in a new file it gets stuck at this line

ActiveChart.SetSourceData Source:=Range("IDCJAC0002_3007_Data12!$A$1:$P$103")

I'm guessing the issue is that it is trying to use the data from the original file however I just want it to run the same thing on my current active sheet. I guess all I need to do is edit the source properly wherever necessary along the entire macros but I am clueless with all the commands and would appreciate any help here!!

here is the macros i have recorded:
Sub chart()
'
' chart Macro
' chart
'
' Keyboard Shortcut: Ctrl+f
'
Range("B2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range("IDCJAC0002_3007_Data12!$A$1:$P$103")
ActiveChart.Legend.Select
ActiveChart.ChartArea.Select
ActiveChart.Parent.Cut
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Jan"
Range("C4").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Name = "=""data"""
ActiveChart.SeriesCollection(1).XValues = "=IDCJAC0002_3007_Data12!$C$2:$C$145"
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$D$2:$D$152"
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MaximumScale = 45
ActiveChart.Axes(xlValue).MinimumScale = 20
ActiveChart.Axes(xlValue).MaximumScale = 24
ActiveChart.Axes(xlValue).MaximumScale = 40
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Feb"
Range("C3").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$E$2:$E$158"
Sheets.Add After:=Sheets(Sheets.Count)
ActiveCell.FormulaR1C1 = ""
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Mar"
Range("B3").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$F$2:$F$153"
Sheets("IDCJAC0002_3007_Data12").Select
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 36
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Apr"
Range("B3").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$G$2:$G$144"
Sheets("Apr").Select
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Sheets("IDCJAC0002_3007_Data12").Select
ActiveWindow.SmallScroll Down:=-48
Sheets("IDCJAC0002_3007_Data12").Select
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "May"
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$H$2:$H$129"
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "Jun"
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$I$2:$I$138"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "Jul"
Range("A2").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$J$2:$J$135"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "Aug"
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$K$2:$K$149"
Sheets("Aug").Select
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet10").Select
Sheets("Sheet10").Name = "Sep"
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$L$2:$L$145"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet11").Select
Sheets("Sheet11").Name = "Oct"
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$M$2:$M$152"
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet12").Select
Sheets("Sheet12").Name = "Nov"
Range("A2").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$N$2:$N$149"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet13").Select
Sheets("Sheet13").Name = "Dec"
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$O$2:$O$173"
Sheets.Add After:=Sheets(Sheets.Count)
ActiveCell.FormulaR1C1 = ""
Sheets("Sheet14").Select
Sheets("Sheet14").Name = "Annual"
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=IDCJAC0002_3007_Data12!$P$2:$P$153"
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Dont worry I think I have it :)

horribly useless vba but as long as it does the job and i finish this thesis I am happy man...

Sub chart()
'
' chart Macro
' chart
'
' Keyboard Shortcut: Ctrl+f
'
ActiveSheet.Name = "Data"
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=ActiveSheet.Range("$A:$B")
ActiveChart.Legend.Select
ActiveChart.ChartArea.Select
ActiveChart.Parent.Cut
Sheets.Add.Name = "Jan"
Sheets("Jan").Activate
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Name = "=""data"""
ActiveChart.SeriesCollection(1).XValues = "=Data!$C$2:$C$145"
ActiveChart.SeriesCollection(1).Values = "=Data!$D$2:$D$152"
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 20
ActiveChart.Axes(xlValue).MaximumScale = 40
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Sheets.Add.Name = "Feb"
Sheets("Feb").Activate
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=Data!$E$2:$E$158"
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Sheets.Add.Name = "Mar"
Sheets("Mar").Activate
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=Data!$F$2:$F$158"
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
351
RoryA
RoryA
P
  • Question Question
Help with Vba Code
Replies
3
Views
476
Coyotex3
C
T
  • Question Question
Help with VBA issue, mysterious to me
Replies
1
Views
84
TomCon
T
K
  • Solved
Jump to cell after clicking message box ok
Replies
3
Views
288
dreid1011
dreid1011
D
  • Question Question
Error Retrieving data with XLOOKUP after using VBA to copy to source cells.
Replies
6
Views
410
Alex Blakenburg
A

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

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