vba change just the sheet cource for multiple charts

hmmm...

Board Regular
Joined
Feb 24, 2003
Messages
104
vba change just the source sheet for multiple charts

Hi, I have many charts on a worksheet; the source data are on another sheet. I have a second data sheet with similar data; I want to copy all the charts to a second chart sheet, and have the charts refer to the second data sheet. All the source data ranges would stay the same; just the sheet would change. How to edit with VBA the source sheet for all of these copied charts?

I've tried this with macro-recording while I manually make the changes. This results in SetSourceData. I have tried to loop this in VBA but I don't see how to change the sheet but keep the source ranges as they are (it's different for each chart).

Any ideas? Thanks!
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Re: vba change just the source sheet for multiple charts

Try this code. From your description I've assumed that the charts are on a normal worksheet, not on a chart sheet which can contain only charts. You just need to edit the code where indicated by CHANGE THIS to suit your sheet names.

Code:
Sub Copy_Charts_and_Change_Source_Data()

    Dim DataSheetName1 As String, DataSheetName2 As String
    Dim sourceChartSheet As Worksheet, destChartSheet As Worksheet
    Dim chartObj As ChartObject
    Dim chartObjCopy As ChartObject
    Dim chSeries As Series
    Dim chartIndex As Integer
    
    'Names of the 2 data sheets
    
    DataSheetName1 = "Data1"   'CHANGE THIS
    DataSheetName2 = "Data2"   'CHANGE THIS
    
    'Source sheet - the existing charts will be copied from this sheet
    
    Set sourceChartSheet = Sheets("ChartSheet1")   'CHANGE THIS
    
    'Destination sheet - the charts will be copied to this sheet
    
    Set destChartSheet = Sheets("ChartSheet2")   'CHANGE THIS
    
    chartIndex = destChartSheet.ChartObjects.Count
    
    For Each chartObj In sourceChartSheet.ChartObjects
    
        'Copy this chart to destination chart sheet, keeping its position
        
        chartObj.Copy
        destChartSheet.Range(chartObj.TopLeftCell.Address).PasteSpecial xlPasteAll
        chartIndex = chartIndex + 1
        Set chartObjCopy = destChartSheet.ChartObjects(chartIndex)
        chartObjCopy.Left = chartObj.Left
        chartObjCopy.Top = chartObj.Top
        
        'Change the sheet name in all series in the destination chart
        
        For Each chSeries In chartObjCopy.Chart.SeriesCollection
            chSeries.FormulaR1C1 = Replace(chSeries.FormulaR1C1, DataSheetName1, DataSheetName2)
        Next
    
    Next
    
End Sub
 

hmmm...

Board Regular
Joined
Feb 24, 2003
Messages
104
Re: vba change just the source sheet for multiple charts

Thank you, John--

I have tried this and am getting this error:

Run-time error '1004':

The specified dimension is not valid for the current chart type.

Debugging highlights in yellow this line:

Set chartObjCopy = destChartSheet.ChartObjects(chartIndex)

I have plugged in the source files and the chart source and destination sheets. The sheets with the charts are not chart sheets, as you surmised. I have checked the data sheets and they are formatted the same (columns, rows, cell ranges, etc.).

I'll try to figure this out but if you (or others) have an idea on how to proceed with this please let me know.

Thank you!

--Arthur
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Re: vba change just the source sheet for multiple charts

Depending on the contents of the two worksheets, it might be quickest to do this is by hand!

Suppose your worksheet with the charts and the data shown in the charts is Sheet1. Suppose the worksheet with the new data is Sheet2.

Step 1: make a copy of Sheet1: click the tab for Sheet1, press and hold the CTRL key, and drag the tab sideways until you see the cursor show a worksheet with a + sign. Release the mouse and then the CTRL key.

Step 2: switch to Sheet2. Use CTRL+a to select all the cells and CTRL+c to copy the contents.

Step 3: switch to the new worksheet created in step 1, select all cells and paste.

Hi, I have many charts on a worksheet; the source data are on another sheet. I have a second data sheet with similar data; I want to copy all the charts to a second chart sheet, and have the charts refer to the second data sheet. All the source data ranges would stay the same; just the sheet would change. How to edit with VBA the source sheet for all of these copied charts?

I've tried this with macro-recording while I manually make the changes. This results in SetSourceData. I have tried to loop this in VBA but I don't see how to change the sheet but keep the source ranges as they are (it's different for each chart).

Any ideas? Thanks!
 

hmmm...

Board Regular
Joined
Feb 24, 2003
Messages
104

ADVERTISEMENT

Re: vba change just the source sheet for multiple charts

Thanks for the reply. I am all for a simple way to do this. But the charts are on a sheet separate from the data sheet. I can make a copy of the sheet that holds the charts, but all the charts still refer to data on the original data sheet.

If I could use the "Find and Replace" commands on the menu to change the chart references that would be fine. But I don't know how to do this.

Or am I missing something in your suggestion? Thanks!
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Re: vba change just the source sheet for multiple charts

Ah, I missed that part.

Suppose your charts are on Chart1. Currently they plot the data on Data1. You want to show the data on Data2.

Do the below with a copy of your workbook. There are a few circumstances when the below will fail and you don't want a messed up workbook.

On a one time basis, copy the Data1 sheet. Rename the existing Data1 as, say, DataShow and the new sheet as Data1.

Now, copy all the data from Data2 (or any other sheet with a layout *identical* to DataShow), select all the data in DataShow, and paste.

Thanks for the reply. I am all for a simple way to do this. But the charts are on a sheet separate from the data sheet. I can make a copy of the sheet that holds the charts, but all the charts still refer to data on the original data sheet.

If I could use the "Find and Replace" commands on the menu to change the chart references that would be fine. But I don't know how to do this.

Or am I missing something in your suggestion? Thanks!
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Re: vba change just the source sheet for multiple charts

The code works for me in Excel 2003. I tested it with 4 charts on the source chart sheet - area chart with 2 data series, line chart with 2 data series, x-y scatter chart with 1 data series, and line chart with 4 data series.

Run-time error '1004':

The specified dimension is not valid for the current chart type.

Debugging highlights in yellow this line:

Set chartObjCopy = destChartSheet.ChartObjects(chartIndex)
Maybe the cause is here - The specified dimension is not valid for the current chart type - ExcelBanter (last post)
 

hmmm...

Board Regular
Joined
Feb 24, 2003
Messages
104
Re: vba change just the source sheet for multiple charts

tusharm, sorry, but I still couldn't get the simple approach to work--from your instructions I couldn't see how to get one a second worksheet full of charts to refer to the second data sheet--I need to retain both sheets of charts. Sorry if I can't see what you assumed I would see, or if I didn't explain well enough what I needed.

John_w I tried your VBA solution again and read up on some of the terms you've used. Sorry, but it's just a bit better than Greek for me. I tried changing the default chart but I get the same result--the error. Maybe it's a difference between Excel 2003 and 2010 (which I am using).

I found another solution that works for what I need right now: <<http://peltiertech.com/WordPress/change-series-formula-improved-routines/>>. I just copied all 40 charts to the second worksheet and then applied the "Program to Modify All Charts on the Active Sheet" code to it, replacing the 1st sheet name with the second, and it worked fine. In this respect, it did work as a "Find and Replace" for the chart series.

Hopefully some day I'll understand the VBA better; not sure why it's difficult, especially as I have BASIC in my background.

Thanks for your replies!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,492
Members
417,028
Latest member
JFCLUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top