vba change just the sheet cource for multiple charts

[hmmm...]

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!

 

[John_w]

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.

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...]

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]

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...]

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]

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]

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...]

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!