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:
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
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