Problem Related to drag chart in Excel

[prince]

Hello Sir, When i drag the group of 27 rows then the Chart in excel not showing to display the update record. Kindly guide me how can i see the new record when i drag the group of rows.
Kindly see the sheet 2 where i have tried to data through the chart. But its not going update during the drag.
With Regards,
Prince

[HansV]

As you have found, the source data of a chart aren't changed when you copy the chart. You will have to edit the source data for each chart separately.
- Select a chart.
- On the Design tab of the ribbon (under Chart Tools), in the Data group, click Select Data.
- Select the correct data range.

S1039.png

- Click OK.
- Repeat for each chart.

[Rudi]

If you have lots of charts, you can try this macro.

Code: Select all

Sub SetChartData()
Dim oCh As ChartObject
Dim rgA As Range
Dim r As Long
    Application.ScreenUpdating = False
    For Each oCh In ActiveSheet.ChartObjects
        Set rgA = oCh.TopLeftCell
        r = rgA.Row
        Set rgA = Range("B" & r - 4 & ":C" & r - 2)
        oCh.Chart.SetSourceData Source:=rgA
    Next
    Application.ScreenUpdating = True
End Sub

The macro gets the cell reference of the top left corner of the chart and calculates the location by moving 4 rows up and getting the reference in columns B and C.
It then sets the source range of the chart with the newly calculated range.
Needless to say that it relies wholly on the fact that the data structure remains a similar structure, but that should be the case if you are auto-filling the blocks each time.

[prince]

Mr. Rudi , wat an idea ! its working perfectly . Thanks a lot .
Mr. Hans , you have also suggested the good idea. Thanks to you also.
With Regards,
Prince

[prince]

Mr. Rudi, Yesterday i was tried you macro code & it was working perfectly but when i opened this file again now its not working . what i do now . Kindly suggest me.
Regards,
Prince

[HansV]

Have you changed the layout of your sheet?

Do you get an error message? If so what does it say?

[prince]

No Mr. Hans , I got the solution now. Actually each and every time when i will drag the group of rows after that when i will run the macro code then it showing the effects on sheet.
Thanks for communication Mr. Hans.
Regards,
Prince