PDA

View Full Version : how i reduce size of the prodedure


karurkumar
06-17-2017, 03:04 AM
Sub CHART6mt()


Windows("02AA-- DATA 3 mt--1.xlsm").Activate
Sheets("Chart6").Select





Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.PrintCommunication = False



ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
ActiveChart.FullSeriesCollection(1).Values = "='sheet 1'!$C$40500:$C$41900"
ActiveChart.FullSeriesCollection(2).Values = "='sheet 1'!$I$40500:$I$41900"
ActiveChart.FullSeriesCollection(3).Values = "='sheet 1'!$S$40500:$S$41900"
ActiveChart.FullSeriesCollection(4).Values = "='sheet 1'!$T$40500:$T$41900"
ActiveChart.FullSeriesCollection(5).Values = "='sheet 1'!$U$40500:$U$41900"
ActiveChart.FullSeriesCollection(6).Values = "='sheet 1'!$V$40500:$V$41900"
ActiveChart.FullSeriesCollection(7).Values = "='sheet 1'!$W$40500:$W$41900"
ActiveChart.FullSeriesCollection(8).Values = "='sheet 1'!$X$40500:$X$41900"





ActiveChart.FullSeriesCollection(8).Format.Line.Visible = msoTrue
ActiveChart.FullSeriesCollection(8).Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
ActiveChart.FullSeriesCollection(8).Format.Line.ForeColor.TintAndShade = 0
ActiveChart.FullSeriesCollection(8).Format.Line.ForeColor.Brightness = 0
ActiveChart.FullSeriesCollection(8).Format.Line.Transparency = 0
ActiveChart.FullSeriesCollection(8).Format.Line.Weight = 0.5
ActiveChart.FullSeriesCollection(8).Format.Line.DashStyle = msoLineSysDash

ActiveChart.FullSeriesCollection(7).Format.Line.Visible = msoTrue
ActiveChart.FullSeriesCollection(7).Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
ActiveChart.FullSeriesCollection(7).Format.Line.ForeColor.TintAndShade = 0
ActiveChart.FullSeriesCollection(7).Format.Line.ForeColor.Brightness = 0
ActiveChart.FullSeriesCollection(7).Format.Line.Transparency = 0
ActiveChart.FullSeriesCollection(7).Format.Line.Weight = 0.5
ActiveChart.FullSeriesCollection(7).Format.Line.DashStyle = msoLineSysDash

ActiveChart.FullSeriesCollection(6).Format.Line.Visible = msoTrue
ActiveChart.FullSeriesCollection(6).Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
ActiveChart.FullSeriesCollection(6).Format.Line.ForeColor.TintAndShade = 0
ActiveChart.FullSeriesCollection(6).Format.Line.ForeColor.Brightness = 0
ActiveChart.FullSeriesCollection(6).Format.Line.Transparency = 0
ActiveChart.FullSeriesCollection(6).Format.Line.Weight = 0.5
ActiveChart.FullSeriesCollection(6).Format.Line.DashStyle = msoLineSysDash

ActiveChart.FullSeriesCollection(5).Format.Line.Visible = msoTrue
ActiveChart.FullSeriesCollection(5).Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
ActiveChart.FullSeriesCollection(5).Format.Line.ForeColor.TintAndShade = 0
ActiveChart.FullSeriesCollection(5).Format.Line.ForeColor.Brightness = 0
ActiveChart.FullSeriesCollection(5).Format.Line.Transparency = 0
ActiveChart.FullSeriesCollection(5).Format.Line.Weight = 0.5
ActiveChart.FullSeriesCollection(5).Format.Line.DashStyle = msoLineSysDash

ActiveChart.FullSeriesCollection(4).Format.Line.Visible = msoTrue
ActiveChart.FullSeriesCollection(4).Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
ActiveChart.FullSeriesCollection(4).Format.Line.ForeColor.TintAndShade = 0
ActiveChart.FullSeriesCollection(4).Format.Line.ForeColor.Brightness = 0
ActiveChart.FullSeriesCollection(4).Format.Line.Transparency = 0
ActiveChart.FullSeriesCollection(4).Format.Line.Weight = 0.5
ActiveChart.FullSeriesCollection(4).Format.Line.DashStyle = msoLineSysDash

ActiveChart.FullSeriesCollection(3).Format.Line.Visible = msoTrue
ActiveChart.FullSeriesCollection(3).Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
ActiveChart.FullSeriesCollection(3).Format.Line.ForeColor.TintAndShade = 0
ActiveChart.FullSeriesCollection(3).Format.Line.ForeColor.Brightness = 0
ActiveChart.FullSeriesCollection(3).Format.Line.Transparency = 0
ActiveChart.FullSeriesCollection(3).Format.Line.Weight = 0.5
ActiveChart.FullSeriesCollection(3).Format.Line.DashStyle = msoLineSysDash

'RSEX WAVE

ActiveChart.FullSeriesCollection(2).Format.Line.Visible = msoTrue
ActiveChart.FullSeriesCollection(2).Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText2
ActiveChart.FullSeriesCollection(2).Format.Line.ForeColor.TintAndShade = 0
ActiveChart.FullSeriesCollection(2).Format.Line.ForeColor.Brightness = 0
ActiveChart.FullSeriesCollection(2).Format.Line.Transparency = 0
ActiveChart.FullSeriesCollection(2).Format.Line.Weight = 3
ActiveChart.FullSeriesCollection(2).Format.Line.DashStyle = msoLineSolid

'price

ActiveChart.FullSeriesCollection(1).Format.Line.Visible = msoTrue
ActiveChart.FullSeriesCollection(1).Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
ActiveChart.FullSeriesCollection(1).Format.Line.ForeColor.TintAndShade = 0
ActiveChart.FullSeriesCollection(1).Format.Line.ForeColor.Brightness = 0
ActiveChart.FullSeriesCollection(1).Format.Line.Transparency = 0
ActiveChart.FullSeriesCollection(1).Format.Line.Weight = 2
ActiveChart.FullSeriesCollection(1).Format.Line.DashStyle = msoLineSolid

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.PrintCommunication = True

End Sub
Bob Phillips
06-17-2017, 03:52 AM
Sub CHART6mt()

Windows("02AA-- DATA 3 mt--1.xlsm").Activate
Sheets("Chart6").Select

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.PrintCommunication = False

With ActiveChart

.ChartArea.Select
.PlotArea.Select
.FullSeriesCollection(1).Values = "='sheet 1'!$C$40500:$C$41900"
.FullSeriesCollection(2).Values = "='sheet 1'!$I$40500:$I$41900"
.FullSeriesCollection(3).Values = "='sheet 1'!$S$40500:$S$41900"
.FullSeriesCollection(4).Values = "='sheet 1'!$T$40500:$T$41900"
.FullSeriesCollection(5).Values = "='sheet 1'!$U$40500:$U$41900"
.FullSeriesCollection(6).Values = "='sheet 1'!$V$40500:$V$41900"
.FullSeriesCollection(7).Values = "='sheet 1'!$W$40500:$W$41900"
.FullSeriesCollection(8).Values = "='sheet 1'!$X$40500:$X$41900"

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

'RSEX WAVE
Call SeriesFormat(.FullSeriesCollection(2), msoThemeColorText2, msoLineSolid, 3)

'price
Call SeriesFormat(.FullSeriesCollection(1), msoThemeColorText1, msoLineSolid, 2)
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.PrintCommunication = True

End Sub

Public Function SeriesFormat( _
ByRef ThisSeries As SeriesCollection, _
ByVal CI As MsoThemeColorIndex, _
ByVal LineStyle As MsoLineDashStyle, _
ByVal LineWeight As Long)


With ThisSeries

.Format.Line.Visible = msoTrue
.Format.Line.ForeColor.ObjectThemeColor = CI
.Format.Line.ForeColor.TintAndShade = 0
.Format.Line.ForeColor.Brightness = 0
.Format.Line.Transparency = 0
.Format.Line.Weight = LineWeight
.Format.Line.DashStyle = LineStyle
End With
End Function
MINCUS1308
06-21-2017, 07:35 AM
I might be wrong but I think you meant to change the 8's?



Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)




Call SeriesFormat(.FullSeriesCollection(8), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(7), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(6), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(5), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(4), msoThemeColorText1, msoLineSysDash, 0.5)

Call SeriesFormat(.FullSeriesCollection(3), msoThemeColorText1, msoLineSysDash, 0.5)
SamT
06-21-2017, 12:57 PM
Taking xld's excellent work to the nrxt level
Option Explicit

Sub CHARTVBAExpress()

Windows("02AA-- DATA 3 mt--1.xlsm").Activate
Sheets("Chart6").Select

Dim DataRows As Range
Dim DataCols(1 To 8) As Variant
Dim i As Long

Set DataRows = Sheets("Sheet1").Range("$40500:$41900")
DataCols = Array(3, 9, 19, 20, 21, 22, 23, 24)

SpeedUp

With ActiveChart.ChartArea.PlotArea.FullSeriesCollection
For i = 1 To 8
.Item(i).Values = DataRows.Columns(DataCols(i))
If i > 2 Then Call SeriesFormat(.Item(i), msoThemeColorText1, msoLineSysDash, 0.5)
Next
'RSEX WAVE
Call SeriesFormat(.Item(2), msoThemeColorText2, msoLineSolid, 3)
'price
Call SeriesFormat(.Item(1), msoThemeColorText1, msoLineSolid, 2)
End With

SpeedUp False
End Sub



Public Function SpeedUp(Optional YesNo As Boolean = True)
'Leave YesNo empty for speed,set False for normal operation
With Application
If YesNo Then
.Calculation = xlCalculationManual
Else: Calculation = xlCalculationAutomatic
End If
.ScreenUpdating = Not YesNo
.DisplayStatusBar = Not YesNo
.EnableEvents = Not YesNo
.PrintCommunication = Not YesNo
End With
End Function



Public Function SeriesFormat( _
ByRef ThisSeries As SeriesCollection, _
ByVal CI As MsoThemeColorIndex, _
ByVal LineStyle As MsoLineDashStyle, _
ByVal LineWeight As Long)

With ThisSeries.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = CI
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Weight = LineWeight
.DashStyle = LineStyle
End With
End Function
karurkumar
06-21-2017, 08:28 PM
thanks xld , mincus1308 and samT thanks all. there i find a way in case code as below



Sub CHART6mt()


Windows("02AA-- DATA 3 mt--1.xlsm").Activate
Sheets("Chart6").Select

With Application
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.PrintCommunication = False
End With
With ActiveChart
.ChartArea.Select
.PlotArea.Select

ActiveChart.FullSeriesCollection(1).Values = "='sheet 1'!$C$41100:$C$42350"
ActiveChart.FullSeriesCollection(2).Values = "='sheet 1'!$I$41100:$I$42350"
ActiveChart.FullSeriesCollection(3).Values = "='sheet 1'!$S$41100:$S$42350"
ActiveChart.FullSeriesCollection(4).Values = "='sheet 1'!$T$41100:$T$42350"
ActiveChart.FullSeriesCollection(5).Values = "='sheet 1'!$U$41100:$U$42350"
ActiveChart.FullSeriesCollection(6).Values = "='sheet 1'!$V$41100:$V$42350"
ActiveChart.FullSeriesCollection(7).Values = "='sheet 1'!$W$41100:$W$42350"
ActiveChart.FullSeriesCollection(8).Values = "='sheet 1'!$X$41100:$X$42350"
ActiveChart.FullSeriesCollection(9).Values = "='sheet 1'!$Y$41100:$Y$42350"
ActiveChart.FullSeriesCollection(10).Values = "='sheet 1'!$Z$41100:$Z$42350"
ActiveChart.FullSeriesCollection(11).Values = "='sheet 1'!$AC$41100:$AC$42350"
ActiveChart.FullSeriesCollection(12).Values = "='sheet 1'!$AJ$41100:$AJ$42350"
ActiveChart.FullSeriesCollection(13).Values = "='sheet 1'!$AM$41100:$AM$42350"
ActiveChart.FullSeriesCollection(14).Values = "='sheet 1'!$AO$41100:$AO$42350"
ActiveChart.FullSeriesCollection(15).Values = "='sheet 1'!$AQ$41100:$AQ$42350"
ActiveChart.FullSeriesCollection(16).Values = "='sheet 1'!$AR$41100:$AR$42350"
ActiveChart.FullSeriesCollection(17).Values = "='sheet 1'!$AS$41100:$AS$42350"
ActiveChart.FullSeriesCollection(18).Values = "='sheet 1'!$AT$41100:$AT$42350"
ActiveChart.FullSeriesCollection(19).Values = "='sheet 1'!$AU$41100:$AU$42350"
ActiveChart.FullSeriesCollection(20).Values = "='sheet 1'!$AV$41100:$AV$42350"
ActiveChart.FullSeriesCollection(21).Values = "='sheet 1'!$AW$41100:$AW$42350"
ActiveChart.FullSeriesCollection(22).Values = "='sheet 1'!$AX$41100:$AX$42350"
ActiveChart.FullSeriesCollection(23).Values = "='sheet 1'!$AY$41100:$AY$42350"
ActiveChart.FullSeriesCollection(24).Values = "='sheet 1'!$AZ$41100:$AZ$42350"
ActiveChart.FullSeriesCollection(25).Values = "='sheet 1'!$BA$41100:$BA$42350"
ActiveChart.FullSeriesCollection(26).Values = "='sheet 1'!$BB$41100:$BB$42350"
ActiveChart.FullSeriesCollection(27).Values = "='sheet 1'!$BC$41100:$BC$42350"
ActiveChart.FullSeriesCollection(28).Values = "='sheet 1'!$BD$41100:$BD$42350"
ActiveChart.FullSeriesCollection(29).Values = "='sheet 1'!$BE$41100:$BE$42350"
ActiveChart.FullSeriesCollection(30).Values = "='sheet 1'!$BF$41100:$BF$42350"
ActiveChart.FullSeriesCollection(31).Values = "='sheet 1'!$BG$41100:$BG$42350"
ActiveChart.FullSeriesCollection(32).Values = "='sheet 1'!$BH$41100:$BH$42350"
ActiveChart.FullSeriesCollection(33).Values = "='sheet 1'!$BI$41100:$BI$42350"
ActiveChart.FullSeriesCollection(34).Values = "='sheet 1'!$BJ$41100:$BJ$42350"
ActiveChart.FullSeriesCollection(35).Values = "='sheet 1'!$BK$41100:$BK$42350"
ActiveChart.FullSeriesCollection(36).Values = "='sheet 1'!$BL$41100:$BL$42350"
ActiveChart.FullSeriesCollection(37).Values = "='sheet 1'!$BM$41100:$BM$42350"
ActiveChart.FullSeriesCollection(38).Values = "='sheet 1'!$BN$41100:$BN$42350"
ActiveChart.FullSeriesCollection(39).Values = "='sheet 1'!$BO$41100:$BO$42350"
ActiveChart.FullSeriesCollection(40).Values = "='sheet 1'!$BP$41100:$BP$42350"
ActiveChart.FullSeriesCollection(41).Values = "='sheet 1'!$BQ$41100:$BQ$42350"
ActiveChart.FullSeriesCollection(42).Values = "='sheet 1'!$BR$41100:$BR$42350"
ActiveChart.FullSeriesCollection(43).Values = "='sheet 1'!$BS$41100:$BS$42350"
ActiveChart.FullSeriesCollection(44).Values = "='sheet 1'!$BT$41100:$BT$42350"
ActiveChart.FullSeriesCollection(45).Values = "='sheet 1'!$BU$41100:$BU$42350"
ActiveChart.FullSeriesCollection(46).Values = "='sheet 1'!$BV$41100:$BV$42350"
ActiveChart.FullSeriesCollection(47).Values = "='sheet 1'!$BW$41100:$BW$42350"
ActiveChart.FullSeriesCollection(48).Values = "='sheet 1'!$BX$41100:$BX$42350"
ActiveChart.FullSeriesCollection(49).Values = "='sheet 1'!$BY$41100:$BY$42350"
ActiveChart.FullSeriesCollection(50).Values = "='sheet 1'!$BZ$41100:$BZ$42350"
ActiveChart.FullSeriesCollection(51).Values = "='sheet 1'!$CA$41100:$CA$42350"
ActiveChart.FullSeriesCollection(52).Values = "='sheet 1'!$CB$41100:$CB$42350"
ActiveChart.FullSeriesCollection(53).Values = "='sheet 1'!$CC$41100:$CC$42350"
ActiveChart.FullSeriesCollection(54).Values = "='sheet 1'!$CD$41100:$CD$42350"
ActiveChart.FullSeriesCollection(55).Values = "='sheet 1'!$CE$41100:$CE$42350"
ActiveChart.FullSeriesCollection(56).Values = "='sheet 1'!$CF$41100:$CF$42350"
ActiveChart.FullSeriesCollection(57).Values = "='sheet 1'!$CG$41100:$CG$42350"
ActiveChart.FullSeriesCollection(58).Values = "='sheet 1'!$CH$41100:$CH$42350"
ActiveChart.FullSeriesCollection(59).Values = "='sheet 1'!$CI$41100:$CI$42350"
ActiveChart.FullSeriesCollection(60).Values = "='sheet 1'!$CJ$41100:$CJ$42350"
ActiveChart.FullSeriesCollection(61).Values = "='sheet 1'!$CK$41100:$CK$42350"
ActiveChart.FullSeriesCollection(62).Values = "='sheet 1'!$CL$41100:$CL$42350"
ActiveChart.FullSeriesCollection(63).Values = "='sheet 1'!$CM$41100:$CM$42350"
ActiveChart.FullSeriesCollection(64).Values = "='sheet 1'!$CN$41100:$CN$42350"
ActiveChart.FullSeriesCollection(65).Values = "='sheet 1'!$CO$41100:$CO$42350"
ActiveChart.FullSeriesCollection(66).Values = "='sheet 1'!$CP$41100:$CP$42350"
ActiveChart.FullSeriesCollection(67).Values = "='sheet 1'!$CQ$41100:$CQ$42350"
ActiveChart.FullSeriesCollection(68).Values = "='sheet 1'!$CR$41100:$CR$42350"
ActiveChart.FullSeriesCollection(69).Values = "='sheet 1'!$CS$41100:$CS$42350"
ActiveChart.FullSeriesCollection(70).Values = "='sheet 1'!$CT$41100:$CT$42350"
ActiveChart.FullSeriesCollection(71).Values = "='sheet 1'!$CU$41100:$CU$42350"
ActiveChart.FullSeriesCollection(72).Values = "='sheet 1'!$CV$41100:$CV$42350"
ActiveChart.FullSeriesCollection(73).Values = "='sheet 1'!$CW$41100:$CW$42350"
ActiveChart.FullSeriesCollection(74).Values = "='sheet 1'!$CX$41100:$CX$42350"
ActiveChart.FullSeriesCollection(75).Values = "='sheet 1'!$CY$41100:$CY$42350"
ActiveChart.FullSeriesCollection(76).Values = "='sheet 1'!$CZ$41100:$CZ$42350"
ActiveChart.FullSeriesCollection(77).Values = "='sheet 1'!$DA$41100:$DA$42350"
ActiveChart.FullSeriesCollection(78).Values = "='sheet 1'!$DB$41100:$DB$42350"
ActiveChart.FullSeriesCollection(79).Values = "='sheet 1'!$DC$41100:$DC$42350"
ActiveChart.FullSeriesCollection(80).Values = "='sheet 1'!$DD$41100:$DD$42350"
ActiveChart.FullSeriesCollection(81).Values = "='sheet 1'!$DE$41100:$DE$42350"
ActiveChart.FullSeriesCollection(82).Values = "='sheet 1'!$DF$41100:$DF$42350"
ActiveChart.FullSeriesCollection(83).Values = "='sheet 1'!$DG$41100:$DG$42350"
ActiveChart.FullSeriesCollection(84).Values = "='sheet 1'!$DH$41100:$DH$42350"
ActiveChart.FullSeriesCollection(85).Values = "='sheet 1'!$DI$41100:$DI$42350"
ActiveChart.FullSeriesCollection(86).Values = "='sheet 1'!$DJ$41100:$DJ$42350"
ActiveChart.FullSeriesCollection(87).Values = "='sheet 1'!$DK$41100:$DK$42350"
ActiveChart.FullSeriesCollection(88).Values = "='sheet 1'!$DL$41100:$DL$42350"
ActiveChart.FullSeriesCollection(89).Values = "='sheet 1'!$DM$41100:$DM$42350"
ActiveChart.FullSeriesCollection(90).Values = "='sheet 1'!$DN$41100:$DN$42350"
ActiveChart.FullSeriesCollection(91).Values = "='sheet 1'!$DO$41100:$DO$42350"
ActiveChart.FullSeriesCollection(92).Values = "='sheet 1'!$DP$41100:$DP$42350"
ActiveChart.FullSeriesCollection(93).Values = "='sheet 1'!$DQ$41100:$DQ$42350"
ActiveChart.FullSeriesCollection(94).Values = "='sheet 1'!$DR$41100:$DR$42350"
ActiveChart.FullSeriesCollection(95).Values = "='sheet 1'!$DS$41100:$DS$42350"
ActiveChart.FullSeriesCollection(96).Values = "='sheet 1'!$DT$41100:$DT$42350"
ActiveChart.FullSeriesCollection(97).Values = "='sheet 1'!$DU$41100:$DU$42350"
ActiveChart.FullSeriesCollection(98).Values = "='sheet 1'!$DV$41100:$DV$42350"
ActiveChart.FullSeriesCollection(99).Values = "='sheet 1'!$DW$41100:$DW$42350"
ActiveChart.FullSeriesCollection(100).Values = "='sheet 1'!$DX$41100:$DX$42350"
ActiveChart.FullSeriesCollection(101).Values = "='sheet 1'!$DY$41100:$DY$42350"
ActiveChart.FullSeriesCollection(102).Values = "='sheet 1'!$DZ$41100:$DZ$42350"
ActiveChart.FullSeriesCollection(103).Values = "='sheet 1'!$EA$41100:$EA$42350"
ActiveChart.FullSeriesCollection(104).Values = "='sheet 1'!$EB$41100:$EB$42350"
ActiveChart.FullSeriesCollection(105).Values = "='sheet 1'!$EC$41100:$EC$42350"
ActiveChart.FullSeriesCollection(106).Values = "='sheet 1'!$ED$41100:$ED$42350"
ActiveChart.FullSeriesCollection(107).Values = "='sheet 1'!$EE$41100:$EE$42350"
ActiveChart.FullSeriesCollection(108).Values = "='sheet 1'!$EF$41100:$EF$42350"
ActiveChart.FullSeriesCollection(109).Values = "='sheet 1'!$EG$41100:$EG$42350"
ActiveChart.FullSeriesCollection(110).Values = "='sheet 1'!$EH$41100:$EH$42350"
ActiveChart.FullSeriesCollection(111).Values = "='sheet 1'!$EI$41100:$EI$42350"
ActiveChart.FullSeriesCollection(112).Values = "='sheet 1'!$EJ$41100:$EJ$42350"
ActiveChart.FullSeriesCollection(113).Values = "='sheet 1'!$EK$41100:$EK$42350"
ActiveChart.FullSeriesCollection(114).Values = "='sheet 1'!$EL$41100:$EL$42350"
ActiveChart.FullSeriesCollection(115).Values = "='sheet 1'!$EM$41100:$EM$42350"
ActiveChart.FullSeriesCollection(116).Values = "='sheet 1'!$EN$41100:$EN$42350"
ActiveChart.FullSeriesCollection(117).Values = "='sheet 1'!$EO$41100:$EO$42350"
ActiveChart.FullSeriesCollection(118).Values = "='sheet 1'!$EP$41100:$EP$42350"
ActiveChart.FullSeriesCollection(119).Values = "='sheet 1'!$EQ$41100:$EQ$42350"
ActiveChart.FullSeriesCollection(120).Values = "='sheet 1'!$ER$41100:$ER$42350"
ActiveChart.FullSeriesCollection(121).Values = "='sheet 1'!$ES$41100:$ES$42350"
ActiveChart.FullSeriesCollection(122).Values = "='sheet 1'!$ET$41100:$ET$42350"
ActiveChart.FullSeriesCollection(123).Values = "='sheet 1'!$EU$41100:$EU$42350"
ActiveChart.FullSeriesCollection(124).Values = "='sheet 1'!$EV$41100:$EV$42350"
ActiveChart.FullSeriesCollection(125).Values = "='sheet 1'!$EW$41100:$EW$42350"
ActiveChart.FullSeriesCollection(126).Values = "='sheet 1'!$EX$41100:$EX$42350"
ActiveChart.FullSeriesCollection(127).Values = "='sheet 1'!$EY$41100:$EY$42350"
ActiveChart.FullSeriesCollection(128).Values = "='sheet 1'!$EZ$41100:$EZ$42350"


For x = 1 To 128
Select Case x
Case 1
'price black line
With .FullSeriesCollection(x).Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Weight = 2
.DashStyle = msoLineSolid
End With
Case 2
'RSEX WAVE blue line
With .FullSeriesCollection(x).Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Weight = 3
.DashStyle = msoLineSolid
End With
Case 3 To 18, 20 To 117, 119 To 120, 122, 124 To 128
'red line 1
With .FullSeriesCollection(x).Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Weight = 1
.DashStyle = msoLineSysDash
End With
Case 19, 121
'red line 2
With .FullSeriesCollection(x).Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Weight = 2
.DashStyle = msoLineSysDash
End With
Case 118
'green line
With .FullSeriesCollection(x).Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 255, 0)
.Transparency = 0
.Weight = 1
.DashStyle = msoLineSysDash
End With

Case 123
'black line
With .FullSeriesCollection(x).Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Weight = 1
.DashStyle = msoLineSysDash
End With
End Select
Next x
End With

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayStatusBar = True
.EnableEvents = True
.PrintCommunication = True
End With
End Sub











because i need to add more series daily so its easy for me . is there any way reduce the above code??.
p45cal
06-22-2017, 06:08 AM
regarding the first part:
With ActiveChart
.ChartArea.Select
.PlotArea.Select

ActiveChart.FullSeriesCollection(1).Values = "='sheet 1'!$C$41100:$C$42350"
ActiveChart.FullSeriesCollection(2).Values = "='sheet 1'!$I$41100:$I$42350"
ActiveChart.FullSeriesCollection(3).Values = "='sheet 1'!$S$41100:$S$42350"
'…
'…
ActiveChart.FullSeriesCollection(126).Values = "='sheet 1'!$EX$41100:$EX$42350"
ActiveChart.FullSeriesCollection(127).Values = "='sheet 1'!$EY$41100:$EY$42350"
ActiveChart.FullSeriesCollection(128).Values = "='sheet 1'!$EZ$41100:$EZ$42350"
try replacing it with the likes of:
zzz = Array("C", "I", "S", "T", "U", "V", "W", "X", "Y", "Z", "AC", "AJ", "AM", "AO", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ", "EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU", "EV", "EW", "EX", "EY", "EZ")
i = LBound(zzz)
With ActiveChart
'For Each sery In .SeriesCollection
For Each sery In .FullSeriesCollection 'I haven't got FullSeriesCollection in my version of Excel, so maybe it should be the commented-out line above?
sery.Values = "='sheet 1'!" & zzz(i) & "41100:" & zzz(i) & "42350"
i = i + 1
Next sery
p45cal
06-22-2017, 06:13 AM
Include links when you cross-post!
http://www.excelguru.ca/content.php?184
Grrr.