Solved: VBA to update source data for excel charts

[kevvukeka]

Hi All,

I am working excel 2010 version. I have a report for around 15 countries and 20 charts for each country. I need to update the source data by deleting. Q1 2012 and adding Q1 2013 to it. I n each sheet Q1 2013 data is present in column "AK".

At present the charts show 4 quarters data for 2012. I need to remove Q1 2012 and add Q1 2013 to it.maintaining 4 quarters..

I am beginner to VBA, kindly help.

Thanks & Regards,
Praveen

[SamT]

Prepare an example to upload for us:

Copy two country sheets to a new book
With the new book:
Delete all the Rows from Row(11) down;
Change all personal and proprietary information to fake data;
Make a copy of one of the sheets and rename it to "Results1";
Manually make "Results1" look like you want it to look, Headers, "Q1," Q2," etc. (Actual data values don't matter.);
Upload it here.

[kevvukeka]

Hi Sam,

Thanks for your reply. Please find attached the excel sheet with fake data.

I have one sheet with dashboard data and three other sheets with charts in it.

Now in Awareness, consideration and Preference sheets I need to remove Q12012 data and add Q12013 data in the charts.

Can you help me with some VBA that would update the above requirement in all charts with one go.

Thanks in advance.

Regards,
Praveen

[SamT]

kevvukeka (http://www.vbaexpress.com/forum/member.php?u=49280),

OK, I see your problem. You need to make the Series Formula or the DashBoard Data sheet dynamic.

I am no expert on Charts, but...

Refering to the data sheet: Some questions do not apply.

Q) Do you store the data for all quarters for all years?

Q)Do you just store the data for the previous 4 Qtrs plus the current Qtr?

Q)Is the Current Qtr (Q12013+) always in column("AK")?

Q) Are the previous four Qtrs always in the same 4 columns?

[kevvukeka]

Hi Sam,

Here are my responses:

a)Do you store the data for all quarters for all years? Yes we store it.

Q)Do you just store the data for the previous 4 Qtrs plus the current Qtr? No we store the entire data.


Q)Is the Current Qtr (Q12013+) always in column("AK")?Yes


Q) Are the previous four Qtrs always in the same 4 columns?Yes


What we do basically is, we just show 4 quarters of data every quarter end. for eg:
Q1,Q2,Q3,Q4 or Q2, Q3,Q4, Q1(next year)

[SamT]

kevvukeka (http://www.vbaexpress.com/forum/member.php?u=49280),

I know i am asking a lot of questions, but the hardest part of designing a solution is to understand the structure of the data one is working with.

About the location where all the data is stored:

Is it in a workbook? If it's in a database, I'm already out of my filed of knowledge.

On one sheet or many?

If one sheet, how are the column labels structured ? (22012, 32012... Q22012, Q32012... Q2 2012...Q 2 2012... etc)

Are the Rows Labeled? How?

If one sheet, are the Qtr's data stored is sequential columns, so that the latest Qtr stored is in the last used column?

If many sheets, explain the structure.

How does the Current Qtr data get put in column ("AK")?

How do the previous 4 Qtrs data get into their columns?

[kevvukeka]

Hi Sam,

For each country we have 1 worksheet. In that worksheets we have 1 sheet named as "Dashboard Country Name". This sheet si followed by several other sheets with charts in them.

All the charts source data lies in "Dashboard Country Name" sheet. I am attaching a sample workbook. Each sheet name represents a category. The categories are mentioned in Column A of "Dashboard Country Name sheet".

So for Sheet with Awareness chart. Data source would be

Dashboard India $A2:$A10, Col F, J, N with respective rows selected.

For sheet with Consideration, Data source would be

Dashboard India $A11:A19, Col F, J, N with respective rows selected.

The data in the "Dashboard sheet" is added every quarter.

So every quarter we need to delete the first quarter in the chart and add the latest. for e.g in the current charts. Remove Q1 2012 data and add Q1 2013 data.

The column lables are shown in the attached excel workbook.

Thanks for your time.

Regards,
Praveen

[kevvukeka]

Hi Sam,

I tried to record the requirement using a macro recorder. Below is the code

Sub Chart()
'
' Chart Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "='DASHBOARD XXXX'!$AK$2"
ActiveChart.SeriesCollection(4).Values = _
"='DASHBOARD XXXX'!$AK$147,'DASHBOARD XXXX'!$AK$148,'DASHBOARD XXXX'!$AK$149,'DASHBOARD XXXX'!$AK$150,'DASHBOARD XXXX'!$AK$151,'DASHBOARD XXXX'!$AK$152"

End Sub


Can we modify the above the code so that I know the seriescollection(1).values in which I can replace the col Reference to AK?

That will do the task.

Regards,
Praveen

[SamT]

Sounds like it would be easier to "Step" the series.values, than delete and add series.
With ActiveChart
.SeriesCollection(1).Values =ActiveChart.SeriesCollection(2).Values
.SeriesCollection(2).Values =ActiveChart.SeriesCollection(3).Values
.SeriesCollection(3).Values =ActiveChart.SeriesCollection(4).Values
.SeriesCollection(4).Values =NewValue

[mdmackillop]

Maybe a "sledgehammer" solution, needing a bit more work for general application.
Option Explicit

Sub NewData()
Dim i As Long
For i = 2 To 4 'Generalise @@@@@@@@@@@@@
Sheets(i).Select
SetNewData ActiveChart
Next
End Sub

Sub SetNewData(MyChart As Chart)

Dim S As Series, Sht As String, Sh As String
Dim r As Range
Dim ws As Worksheet
Dim Source As String
Dim c As Range
Dim NewData As Range
Dim i As Long, j As Long, k As Long
Dim x

If MyChart Is Nothing Then Exit Sub

For j = 1 To 4 'Generalise @@@@@@@@@@@@

Set S = MyChart.SeriesCollection(j)
Source = S.Formula

'Adjustment required for single string name @@@@@@@@@@@@@@@@@@@@
'Split out source sheet
Sh = (Split(Split(Source, "!")(0), "(")(1))
'Sheet name only
Set ws = Sheets(Split(Sh, "'")(1))
'Sheet reference
Sht = Sh & "!"

x = Application.Substitute(Source, Sht, "")
x = Application.Substitute(x, "=SERIES", "")
x = Application.Substitute(x, "(", "")
x = Application.Substitute(x, ")", "")
x = Left(x, Len(x) - 2)
x = Split(x, ",")
k = 0
'Initialise range
If r Is Nothing Then
Set r = ws.Range(x(0))
k = 1
End If
'Form data range
For i = k To UBound(x)
Set r = Union(r, ws.Range(x(i)))
Next

Next

'Add Top Left cell
Set r = Union(r, ws.Cells(1, 2))

'Initialize new data range
Set NewData = ws.Cells(1, 2)
'Create new data range
For Each c In r.Columns
If c.Column <> 2 Then
Set NewData = Union(NewData, c.Offset(, 4))
Else
Set NewData = Union(NewData, c)
End If
Next

'Reset chart data
MyChart.SetSourceData NewData

End Sub

[kevvukeka]

Hi Sam,

The idea of stepping the series looks good but for the new series, Is there a way we can just replace the row references for the series collection(4), else i need to manually give the row details every time. the row details for the all the series collection is same, we just have to replace columns.

Thanks & Regards,
Praveen

[SamT]

Private Function NewValue() As String
Dim FirstCol As String
Dim SecondCol As String

FirstCol = "R"
SecondCol = "B"

NewValue = "=SERIES('Dashboard data - India'!$" & FirstCol & "$1," _
& "'Dashboard data - India'!$" & SecondCol & "$4:$" & SecondCol & "$7," _
& "'Dashboard data - India'!$" & FirstCol & "$4:$" & FirstCol & "$7,2)"
End Function

[kevvukeka]

Hi,

Based on above suggestions, I was able to write the below code.

can you help me to know whats wrong with this. Its not assigning the string to series collection values as I need.

Sub colref()
Dim s As Series
Dim source As String, sh As String
Set s = ActiveChart.SeriesCollection(4)
source = s.Formula
sh = Replace(source, "AK", "AG")
ActiveChart.SeriesCollection(4).Values = "sh"

End Sub

the string in sh is "=('DASHBOARD HUNGARY'!$AK$14,'DASHBOARD HUNGARY'!$AK$15,'DASHBOARD HUNGARY'!$AK$16,'DASHBOARD HUNGARY'!$AK$17,'DASHBOARD HUNGARY'!$AK$18,'DASHBOARD HUNGARY'!$AK$19)

[kevvukeka]

Hi All,

I got the solution. Replacing value to formula did the trick:

Sub colref()
Dim s As Series
Dim source As String, sh As String, sh1 As String
With ActiveChart
.SeriesCollection(1).formula =ActiveChart.SeriesCollection(2).formula
.SeriesCollection(2).formula =ActiveChart.SeriesCollection(3).formula
.SeriesCollection(3).formula =ActiveChart.SeriesCollection(4).formula
end with
Set s = ActiveChart.SeriesCollection(4)
source = s.Formula
sh = Replace(source, "AF", "AK")
ActiveChart.SeriesCollection(4).Formula = sh

End Sub

Thanks!!

Praveen