Help with a macro to copy a graph and paste it on another graph

S

Sardo

New Member
Joined
Jul 16, 2017
Messages
6
Hello everyone. I often use this forum to find my way around Excel, but this is actualy my first time posting.

Recently I changed position at work and I have a task at hands where a macro would be more than helpful. I am comfortable using excel, but programming macros is a completely new world for me. I know that learnign this takes time and lots of search, but I running late on this assignament. I already spent lots of hours searching this forum and others trying to figure out what I am doing wrong, but I simply couldn't put my code to run the way I want, so any help would be very usefull.

The excel that I am working on has several sheets, but these sheets are not related to each other (there are no references between them).

Each sheet has a structure like this:

[TABLE="width: 1267"]
<tbody>[TR]
[TD]Lot[/TD]
[TD]Parameter A[/TD]
[TD]Parameter B[/TD]
[TD]…[/TD]
[TD]Parameter X…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Parameter A[/TD]
[TD="colspan: 2"]Parameter B[/TD]
[TD]…[/TD]
[TD="colspan: 2"]Parameter X…[/TD]
[/TR]
[TR]
[TD]00116[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]…[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]Average[/TD]
[TD="colspan: 2"]15[/TD]
[TD="colspan: 2"]150[/TD]
[TD]…[/TD]
[TD="colspan: 2"]75[/TD]
[/TR]
[TR]
[TD]00216[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD]…[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]Standard deviation[/TD]
[TD="colspan: 2"]4,47[/TD]
[TD="colspan: 2"]44,72[/TD]
[TD]…[/TD]
[TD="colspan: 2"]22,36[/TD]
[/TR]
[TR]
[TD]00316[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]150[/TD]
[TD]…[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00416[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]…[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD]…[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]00516[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD]…[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg+d[/TD]
[TD="align: right"]19,47[/TD]
[TD="align: right"]19,47[/TD]
[TD="align: right"]194,72[/TD]
[TD="align: right"]194,72[/TD]
[TD]…[/TD]
[TD="align: right"]97,36[/TD]
[TD="align: right"]97,36[/TD]
[/TR]
[TR]
[TD]00117[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]…[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg-d[/TD]
[TD="align: right"]10,53[/TD]
[TD="align: right"]10,53[/TD]
[TD="align: right"]105,28[/TD]
[TD="align: right"]105,28[/TD]
[TD]…[/TD]
[TD="align: right"]52,64[/TD]
[TD="align: right"]52,64[/TD]
[/TR]
[TR]
[TD]00217[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD]…[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg+2d[/TD]
[TD="align: right"]23,94[/TD]
[TD="align: right"]23,94[/TD]
[TD="align: right"]239,44[/TD]
[TD="align: right"]239,44[/TD]
[TD]…[/TD]
[TD="align: right"]119,72[/TD]
[TD="align: right"]119,72[/TD]
[/TR]
[TR]
[TD]00317[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]150[/TD]
[TD]…[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg-2d[/TD]
[TD="align: right"]4,47[/TD]
[TD="align: right"]4,47[/TD]
[TD="align: right"]44,72[/TD]
[TD="align: right"]44,72[/TD]
[TD]…[/TD]
[TD="align: right"]22,36[/TD]
[TD="align: right"]22,36[/TD]
[/TR]
[TR]
[TD]00417[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]…[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg+3d[/TD]
[TD="align: right"]28,42[/TD]
[TD="align: right"]28,42[/TD]
[TD="align: right"]284,16[/TD]
[TD="align: right"]284,16[/TD]
[TD]…[/TD]
[TD="align: right"]142,08[/TD]
[TD="align: right"]142,08[/TD]
[/TR]
[TR]
[TD]00517[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD]…[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg-3d[/TD]
[TD="align: right"]1,58[/TD]
[TD="align: right"]1,58[/TD]
[TD="align: right"]15,84[/TD]
[TD="align: right"]15,84[/TD]
[TD]…[/TD]
[TD="align: right"]7,92[/TD]
[TD="align: right"]7,92[/TD]
[/TR]
[TR]
[TD]00118[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]…[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00218[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD]…[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00318[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]150[/TD]
[TD]…[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00418[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]…[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00518[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD]…[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00119[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]…[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00219[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD]…[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00319[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]150[/TD]
[TD]…[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00419[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]…[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00519[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD]…[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I can have sheets wiht columns for only two or three "Parameters", but other have 30 columns or more, the number of rows usually is more than 500.

I have to make an indiviual line chart for each "Parameter" (Y axis) with the column "Lot" being always the X axis (order is important).
Besides that I have to exhibit on that graph a line for the Average, Average +- Standard Deviation, Average +- 2*SD, Average +- 3*Standar Deviation (I put them on a secondary axis, so it does not mess up the main data - 2 data entries instead of hundreds).

Doing this manually for a graph or two is easy, but I have hundreds of graphs to do. To try to automatize this I started to write the following macro:

Code: 
Sub CreateGraph()
'
' CreateGraph Macro
'
' Shortcut: Ctrl+Shift+G
'


Dim myPoints As Range
Dim myAvg As Range
Dim pts As Object
Dim avg As Object


Set myPoints = Application.InputBox(prompt:="Range Lot, Parameter", Type:=8)
Set pts = ActiveSheet.Shapes.AddChart2
pts.Chart.SetSourceData Source:=myPoints
pts.Chart.ChartType = xlLineMarkers
Name = "pts"


Set myAvg = Application.InputBox(prompt:="Range avg, avg+-sd, avg+-*2sd, avg+-3sd", Type:=8)
Set avg = ActiveSheet.Shapes.AddChart2
avg.Chart.SetSourceData Source:=myAvg
avg.Chart.ChartType = xlLineMarkers


End Sub

This part of the code works without problems, but now I would like to copy graph "avg" and paste it as formulas (paste special) into graph "pts". Every thing that I tried ended up in the debugging window. I think I should activate graph "avg", then copy, then activate graph "pts" and PasteSpecial Format:=3, but I get keeping erros. Can you show me the lines of code that I must write?

Thanks in advance for your help
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

S
  • Question Question
Inventory disposal plan
Replies
3
Views
277
Spaceballs_the_fanboy
S
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
73
welshgasman
welshgasman
A
  • Solved
compare brands between two sheets and show minus and positive values based on date (today)
Replies
6
Views
232
abdo meghari
A
M
  • Question Question
Help with pivot table
Replies
1
Views
355
markdoc5
M
F
  • Question Question
Graph Help
Replies
2
Views
296
Fegal
F

Forum statistics

Threads
1,223,060
Messages
6,169,860
Members
452,286
Latest member
noclue2000

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