Graphing
 

Hello all,
Why oh why oh why can't I get this thing to work.
I have a macro to make a graph but everytime I go into the graph the series
colors and different line types that what I set previously. I have tried
working on this for 3 days and haven't gotten anywhere. Can you help me,
Please. Below is the macro I am using. I hope this help someone in helping
me. Thanks

Sub No8_Chart()
'
'Puts Proper Y-Scale Settings at bottom of graph page
'
Sheet7.unprotect "1dickson"
Sheet111.unprotect "1dickson"
Application.ScreenUpdating = False

If Sheets("test database").Range("A1") < 1 Then _

Application.ScreenUpdating = False
Sheets("Test Database").Select
Range("L27:L500").Select
Selection.Copy
Sheets("#8_chart").Select
Range("G16").Select
ActiveSheet.Paste
'
'QA Tests
'
' Sheets("QA_Verification").Select
' Range("P6:P205").Select
' Selection.Copy
' Sheets("#8_chart").Select
'Range("H16").Select
'ActiveSheet.Paste
'
'Verification Tests
'
' Sheets("QA_Verification").Select
' Range("Q6:Q205").Select
' Selection.Copy
' Sheets("#8_chart").Select
'Range("M16").Select
'ActiveSheet.Paste
'
'Moving Averages
'
Sheets("Moving Averages").Select
Range("I23:I500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#8_chart").Select
Range("H19").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Graph").Select
Range("J7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("#8_chart").Select
Range("L7").Select
Selection.Copy
Sheets("Graph").Select
Range("J9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'
Range("J9").Select
Selection.NumberFormat = "0"
'
Sheets("ac_chart").Select
Range("T12").Select
Selection.Copy
Sheets("Graph").Select
Range("C43").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("ac_chart").Select
Range("U12").Select
Selection.Copy
Sheets("Graph").Select
Range("C44").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E45").Select
Application.CutCopyMode = False
'
'AC-Graph:Draws Graph
'

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("#8_chart").Range("B15:J500"), _
PlotBy:=xlColumns
ActiveWindow.Visible = False
Range("A1").Select

ActiveSheet.DrawingObjects("Chart 1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.2
.MaximumScale = 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
End With
ActiveWindow.Visible = False
Range("A1").Select
'
ActiveSheet.DrawingObjects("Chart 1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MaximumScale = ActiveSheet.Range("C43")
.MinimumScale = ActiveSheet.Range("C44")
.MinorUnit = 1
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
.TickLabels.NumberFormat = "0.0"
End With

Graphing
 

Eric,

I had a very similar issue. I have a sheet that runs a query on our ERP
system. A second sheet is a pivot table based on the returned data and a
third sheet contains a graph with buttons. Depending on which button is
pressed, relevant dat for that department is returned. This is for yield
analysis so I wanted all the inputs to be one colour and all the outputs
another. Every time the data refreshes, the colours return to the default.
On checking the knowledge base, I discovered that this is an issue with Excel
not with my Macro. The KB article suggested writing a macro that reformats
the graph to your requirements. It worked. The only issue I now have is
that there were 4 ins and outs (ie 8 in total) when I wrote the macro, but
this week there are only 6 in total. The macro fails. I am now trying to
find a way of identifying how many data points are on the graph then do it
using a a For While loop.

Good luck

Alan

"Eric" wrote:

Hello all,
Why oh why oh why can't I get this thing to work.
I have a macro to make a graph but everytime I go into the graph the series
colors and different line types that what I set previously. I have tried
working on this for 3 days and haven't gotten anywhere. Can you help me,
Please. Below is the macro I am using. I hope this help someone in helping
me. Thanks

Sub No8_Chart()
'
'Puts Proper Y-Scale Settings at bottom of graph page
'
Sheet7.unprotect "1dickson"
Sheet111.unprotect "1dickson"
Application.ScreenUpdating = False

If Sheets("test database").Range("A1") < 1 Then _

Application.ScreenUpdating = False
Sheets("Test Database").Select
Range("L27:L500").Select
Selection.Copy
Sheets("#8_chart").Select
Range("G16").Select
ActiveSheet.Paste
'
'QA Tests
'
' Sheets("QA_Verification").Select
' Range("P6:P205").Select
' Selection.Copy
' Sheets("#8_chart").Select
'Range("H16").Select
'ActiveSheet.Paste
'
'Verification Tests
'
' Sheets("QA_Verification").Select
' Range("Q6:Q205").Select
' Selection.Copy
' Sheets("#8_chart").Select
'Range("M16").Select
'ActiveSheet.Paste
'
'Moving Averages
'
Sheets("Moving Averages").Select
Range("I23:I500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#8_chart").Select
Range("H19").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Graph").Select
Range("J7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("#8_chart").Select
Range("L7").Select
Selection.Copy
Sheets("Graph").Select
Range("J9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'
Range("J9").Select
Selection.NumberFormat = "0"
'
Sheets("ac_chart").Select
Range("T12").Select
Selection.Copy
Sheets("Graph").Select
Range("C43").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("ac_chart").Select
Range("U12").Select
Selection.Copy
Sheets("Graph").Select
Range("C44").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E45").Select
Application.CutCopyMode = False
'
'AC-Graph:Draws Graph
'

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("#8_chart").Range("B15:J500"), _
PlotBy:=xlColumns
ActiveWindow.Visible = False
Range("A1").Select

ActiveSheet.DrawingObjects("Chart 1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.2
.MaximumScale = 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
End With
ActiveWindow.Visible = False
Range("A1").Select
'
ActiveSheet.DrawingObjects("Chart 1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MaximumScale = ActiveSheet.Range("C43")
.MinimumScale = ActiveSheet.Range("C44")
.MinorUnit = 1
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
.TickLabels.NumberFormat = "0.0"
End With

Graphing
 

If it formats by series:

For iSeries = 1 to ActiveChart.SeriesCollection.Count

If it formats by point:

For iPoint = 1 to ActiveChart.SeriesCollection(1).Points.Count

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Alan B" <Alan wrote in message
...
Eric,

I had a very similar issue. I have a sheet that runs a query on our ERP
system. A second sheet is a pivot table based on the returned data and a
third sheet contains a graph with buttons. Depending on which button is
pressed, relevant dat for that department is returned. This is for yield
analysis so I wanted all the inputs to be one colour and all the outputs
another. Every time the data refreshes, the colours return to the
default.
On checking the knowledge base, I discovered that this is an issue with
Excel
not with my Macro. The KB article suggested writing a macro that
reformats
the graph to your requirements. It worked. The only issue I now have is
that there were 4 ins and outs (ie 8 in total) when I wrote the macro, but
this week there are only 6 in total. The macro fails. I am now trying to
find a way of identifying how many data points are on the graph then do it
using a a For While loop.

Good luck

Alan

"Eric" wrote:

Hello all,
Why oh why oh why can't I get this thing to work.
I have a macro to make a graph but everytime I go into the graph the
series
colors and different line types that what I set previously. I have tried
working on this for 3 days and haven't gotten anywhere. Can you help me,
Please. Below is the macro I am using. I hope this help someone in
helping
me. Thanks

Sub No8_Chart()
'
'Puts Proper Y-Scale Settings at bottom of graph page
'
Sheet7.unprotect "1dickson"
Sheet111.unprotect "1dickson"
Application.ScreenUpdating = False

If Sheets("test database").Range("A1") < 1 Then _

Application.ScreenUpdating = False
Sheets("Test Database").Select
Range("L27:L500").Select
Selection.Copy
Sheets("#8_chart").Select
Range("G16").Select
ActiveSheet.Paste
'
'QA Tests
'
' Sheets("QA_Verification").Select
' Range("P6:P205").Select
' Selection.Copy
' Sheets("#8_chart").Select
'Range("H16").Select
'ActiveSheet.Paste
'
'Verification Tests
'
' Sheets("QA_Verification").Select
' Range("Q6:Q205").Select
' Selection.Copy
' Sheets("#8_chart").Select
'Range("M16").Select
'ActiveSheet.Paste
'
'Moving Averages
'
Sheets("Moving Averages").Select
Range("I23:I500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#8_chart").Select
Range("H19").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("F13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Graph").Select
Range("J7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("#8_chart").Select
Range("L7").Select
Selection.Copy
Sheets("Graph").Select
Range("J9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
'
Range("J9").Select
Selection.NumberFormat = "0"
'
Sheets("ac_chart").Select
Range("T12").Select
Selection.Copy
Sheets("Graph").Select
Range("C43").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("ac_chart").Select
Range("U12").Select
Selection.Copy
Sheets("Graph").Select
Range("C44").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("E45").Select
Application.CutCopyMode = False
'
'AC-Graph:Draws Graph
'

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData
Source:=Sheets("#8_chart").Range("B15:J500"), _
PlotBy:=xlColumns
ActiveWindow.Visible = False
Range("A1").Select

ActiveSheet.DrawingObjects("Chart 1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.2
.MaximumScale = 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
End With
ActiveWindow.Visible = False
Range("A1").Select
'
ActiveSheet.DrawingObjects("Chart 1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MaximumScale = ActiveSheet.Range("C43")
.MinimumScale = ActiveSheet.Range("C44")
.MinorUnit = 1
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
.TickLabels.NumberFormat = "0.0"
End With