Solved: How update tow Chart after import by VBA finish

[lotto009]

Dear All expert
How update 2 Chart after import by VBA finish
-my data since 13/3/2013 to 31/3/2013
-After import data 1/4/2013 to 6/4/2013 complete
-My two chart not update from them and my code cover range($A$1:$M$200000)
-Can see my file and revise code :doh:
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Sheets("KPIs").Range("$A$1:$M$200000").AutoFilter Field:=2, Criteria1:=ComboBox1, Operator:=xlAnd
Calculate
End Sub

[lotto009]

Dear expert VBA
Plese help me and revise code because this's sample my job I have many chart must be update on day
Thank you

[mancubus]

1- this is for populating combobox1 in worksheet "Chart" from unique values in Column B in worksheet "KPIs". put this in a standard module.


Sub Populate_wsCB_with_Unq_Val()

Dim Cl As Range
Dim Coll As New Collection
Dim It
Dim LastRow As Long

With Worksheets("KPIs")
If .FilterMode Then .ShowAllData
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
On Error Resume Next
For Each Cl In .Range("B2:B" & LastRow)
Coll.Add Cl.Value, Cl.Value
Next Cl
On Error GoTo 0
End With

With Worksheets("Chart").OLEObjects("ComboBox1").Object
.Clear
For Each It In Coll
.AddItem It
Next It
End With

End Sub



2- put below code in Worksheets("Chart")'s code module and remove the existing code:

Private Sub ComboBox1_Change()

Dim calc As Long
Dim chtRng As Range

With Application
.ScreenUpdating = False
.DisplayAlerts = False
calc = .Calculation
.Calculation = xlCalculationAutomatic
End With

With Worksheets("KPIs")
.Tab.ColorIndex = 4
.AutoFilterMode = False
.Range("$A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=ComboBox1
Set chtRng = .AutoFilter.Range
End With

With Me
.Tab.ColorIndex = 3
.ChartObjects("Chart 10").Chart.SetSourceData Source:=chtRng, PlotBy:=xlColumns
.ChartObjects("Chart 2").Chart.SetSourceData Source:=chtRng, PlotBy:=xlColumns
End With

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = calc
End With

End Sub




3- delete all the code in Worksheets("KPIs")'s code module

[lotto009]

:help Dear expert mancubus
After I put your code has not corect could you see my file att
- Chart CS_CSSR&CS_CDR from sheet KPIs from colum E&F
- Chart PS_CSSR&PS_CDR from sheet KPIs from colum G&H
- Chart show same date
Cloud you help again
Thank you

[mancubus]

i cannot call myself expert :)
thanks anyway...

in VBE, click "insert", then "module"
copy Populate_wsCB_with_Unq_Val procedure here. remove it from worksheet KPIs's code module



sorry for missing parts.
try this. keep in mind that it works with current data structure. if you change the order of columns then you must update the code accordingly...


Private Sub ComboBox1_Change()

Dim calc As Long
Dim chtRng As Range
Dim cht10 As Chart, cht2 As Chart


With Application
.ScreenUpdating = False
.DisplayAlerts = False
calc = .Calculation
.Calculation = xlCalculationAutomatic
End With

With Worksheets("KPIs")
.Tab.ColorIndex = 4
.AutoFilterMode = False
.Range("$A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=ComboBox1
Set chtRng = .AutoFilter.Range
End With

With Me
.Tab.ColorIndex = 3
Set cht10 = .ChartObjects("Chart 10").Chart
With cht10
.SetSourceData Source:=chtRng 'autofilter range
.SeriesCollection(1).XValues = chtRng.Resize(, 1).Offset(1, 0) 'col A of autofilter range
.SeriesCollection(1).Values = chtRng.Resize(, 1).Offset(1, 4) 'col E of autofilter range
.SeriesCollection(2).Values = chtRng.Resize(, 1).Offset(1, 5) 'col F of autofilter range
.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(3).Delete 'del col G series from chart
.SeriesCollection(3).Delete 'del col H series from chart
End With
Set cht10 = .ChartObjects("Chart 2").Chart
With cht10
.SetSourceData Source:=chtRng
.SeriesCollection(3).XValues = chtRng.Resize(, 1).Offset(1, 0) 'autofilter range
.SeriesCollection(3).Values = chtRng.Resize(, 1).Offset(1, 6) 'col G of autofilter range
.SeriesCollection(4).Values = chtRng.Resize(, 1).Offset(1, 7) 'col H of autofilter range
.SeriesCollection(4).AxisGroup = 2
.SeriesCollection(1).Delete 'del col E series from chart
.SeriesCollection(1).Delete 'del col F series from chart
End With
End With

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = calc
End With

End Sub

[lotto009]

Dear mancubus
Thank you for your kindly support and advise
after put your code verry good more graph
Thankyou krab :rotlaugh: :rotlaugh: :rotlaugh:

[mancubus]

you are welcome.

im glad it helped. so please mark the thread as "solved" from "thread tools" above the first message...

[lotto009]

Dear all
Solved How to update tow chart after import by VBA finsh from our forum
mr .mancubus:friends: