Append sheets to MASTER sheet retaining chart but with series updated?

Hi

I have managed to produce VBA to merge my sheets into a MASTER sheet, including a chart from each sheet, successfully BUT....

The Charts that are copied onto my MASTER sheet retain the series from the original copied from sheets

How can the VBA be modified to also sync the new charts to the MASTER sheet data?

For example:-
Three worksheets - Sample1, SampleXYZ and Master

Sample1 has a table with x-axis data and y-axis data with several values over rows 19-28
Above the table is a scatter chart whose series relate to "Sample1" and the x-axis range and the y-axis range - looks good

Sample1.JPG

SampleXYZ has a table with x-axis data and y-axis data with several values over rows 19-28
Above the table is a scatter chart whose series relate to "SampleXYZ" and the x-axis range and the y-axis range - looks good

SampleXYZ.JPG

After running my VBA code, on sheet "Master" I have all that is on "Sample1" sheet followed by all that is on "SampleXYZ" sheet - again, looks good

Master.JPG

BUT - the charts that are shown on "Master" still relate to Series on "Sample1" and "SampleXYZ". I need the charts on "Master" to relate to the series that they belong to on "Master" (i.e., the first chart should relate to the first copied in table and the second chart should relate to the second copied in table.

Of course, the second chart and table no longer occupy the same rows that they did in the original "SeriesXYZ" sheet as they have been appended to the previous copy in. The table data now occupies rows 48-57

So, not only the sheet name needs replacing but also the data range

Any ideas please? Has to be done with VBA, not manually, as in reality we are talking many worksheets combined into one.

The code I am using is:-

Sub MasterSheet()
Dim ws As Worksheet, LR As Long

Application.ScreenUpdating = False
Sheets("Master").Activate

For Each ws In Worksheets
If ws.Name <> "Master" Then
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("A1:J" & LR).Copy
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
End If
Next ws
End Sub

Ok - well, I have solved it myself - but it's not as pretty as I would like, so if there is a better way then please do tell.

In my particular usage, all of my sheets have a similar format - a scatter chart followed by the data table. In all of my sheets the chart plus data table heading takes up 19 rows so I know that as a fixed element.

The length of the data table can vary, so that is an unknown element that I need to calculate when appending to the "Master"

So my new code is thus, and works fine (albeit not elegant):-

Sub MasterSheet()
Dim ws As Worksheet, LR As Long, totalLR As Long, strTemp As String
Dim srs As Series
Dim idx As Integer

Application.ScreenUpdating = False
Sheets("Master").Activate

For Each ws In Worksheets
If ws.Name <> "Master" Then
idx = idx + 1
FromName = ws.Name
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("A1:J" & LR).Copy
If idx = 1 Then
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(0, 0)
Else
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
End If
''' discover series range
''' There will be multiple Columns, all with same ranges.....
''' Original from and to range:-
ofrRng = "$" & (LR - (LR - 19) + 1) & ":"
otoRng = "$" & LR

''' New from and to range:-
If idx = 1 Then
nfrRng = ofrRng
ntoRng = otoRng
Else
nfrRng = "$" & (totalLR + 20) & ":"
ntoRng = "$" & (totalLR + LR)
End If

totalLR = totalLR + LR

ActiveSheet.ChartObjects(idx).Activate
For Each srs In ActiveChart.SeriesCollection
strTemp = WorksheetFunction.Substitute(srs.Formula, FromName, "Master")
strTemp = WorksheetFunction.Substitute(strTemp, ofrRng, nfrRng)
strTemp = WorksheetFunction.Substitute(strTemp, otoRng, ntoRng)
srs.Formula = strTemp
Next
End If
Next ws
End Sub