Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Chart Search & Replace (2002 SP-2)

    Anyone know of a simple method to search & replace in the source data areas of an Excel chart. I have a large number of 2004 worksheets and associated charts which must be copied, then the copies prepared to receive 2005 data. Microsoft's Find & Replace works great for modifying the sheets, but it's not even available to use in the charts ... and changing "2004" to "2005" manually in some of these charts, could take hours.

    I appreciate any help I can get. Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    If you copy the charts/chart sheets together with the data worksheets, the copied charts will use the copied data as source. You can select multiple sheets using the same techniques as for selecting multiple files in Windows Explorer: click + Shift click on sheet tabs selects a contiguous range of sheets; Ctrl+click on a sheet tab selects/deselects an individual sheet. Once you have selected a series of sheets, you can copy them by Ctrl+dragging the sheet tabs, by selecting Edit | Move or Copy Sheet..., or by right-clicking one of the selected sheet tabs and selecting Move or Copy Sheet...

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    Hans,

    Thanks for the quick response. Unfortunately, that didn't work. When I select both the "2004 Data" and "2004 Charts" using the ctrl+click on the tabs, it permits me to copy both sheets, together, without any difficulty. Microsofts copy method names the copies "2004 Data (2)" and "2004 Charts (2)", respectively, causing me to rename them to "2005 Data" and "2005 Charts", manually -- no big deal. However, "2004 Charts (2)" is still linked to the old data sheet (2004 Data). I still have to change the source links within the charts from the old sheet name to the new sheet name [e.g., from =SERIES('2004 Data'!$C$8,'2004 Data'!$D$5:$G$5,'2004 Data'!$D$8:$G$8,2) to =SERIES('2004 Data (2)'!$C$8,'2004 Data (2)'!$D$5:$G$5,'2004 Data (2)'!$D$8:$G$8,2)].

    I have two workbooks with charts that contain over 100 references to " '2004 Data'! ". So, I'd rather not have to do this manually.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    That is strange. Are you absolutely sure that you copied both sheets in one go? If I do that, the source data of the '2004 Charts (2)' sheet refers to '2004 Data (2)', and this will adapt itself automatically when I rename it to '2005 Data'. If I first copy the data sheet, then copy the chart sheet separately, the source data of the '2004 Charts (2)' sheet still refers to the original '2004 Data' sheet.

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    Is it possible that I'm doing something different than you are?

    I selected the first tab (2004 Data) by clicking on it, then selected 2004 Charts using ctrl+click. Then, from the right click menu, selected "Move or Copy ...", selected the "Create a copy" checkbox, then clicked the "OK" button. This created the duplicate sheets, but the copied chart is referencing the original data sheet.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    I think I understand why we get different results. I thought you had separate chart sheets; if you copy those together with the source worksheets, the references to the source data will be adjusted. The same happens if you have charts embedded on the source worksheet. But you have charts embedded in a worksheet different from the source worksheet instead. In that case, the references to the source data will remain unchanged.

    The following macro will adjust the source of all series in all chart objects in the copied chart sheet. Substitute the actual names where needed:

    Sub AdjustSource()
    Const strSource = "2004 Data"
    Const strTarget = "2005 Data"
    Const strChart = "2005 Charts"

    Dim cht As ChartObject
    Dim ser As Series

    For Each cht In Worksheets(strChart).ChartObjects
    For Each ser In cht.Chart.SeriesCollection
    ser.Formula = Replace(ser.Formula, strSource, strTarget)
    Next ser
    Next cht

    Set ser = Nothing
    Set cht = Nothing
    End Sub

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    You could use my Flexfind for doing that. Download and install it, hit control-shift-h, enter the part that needs replacing and make sure you check the "Objects" checkbox. Maybe also wise to select "Sheet" instead of "book" from the left dropdown.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    Jan Karel,

    Thank you. I was hoping that someone might have created a replacement for Microsoft's utility. I'll try Flexfind.

  9. #9
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    Hans,

    When I run the macro you provided, I get a "Run-time error '9': Subscript out of range" message. Debug shows that it is occurring on the "For Each cht In Worksheets(strChart).ChartObjects" line. I think that this may be due to a misunderstanding between us about how my chart sheet is set up.

    There is only one data sheet and one chart sheet for each year in the workbook. However, there are eight to twelve charts on each chart sheet. I tried to post a sample of the workbook (a single year with only four "companies" and associated charts) to this forum, unfortunately, I cannot make the file small enough (smaller than 125 kb) to post without making it meaningless and non-functional.

    Hope this is enough info to make things clearer.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    What is the value of "strChart" and do you have a Worksheet with that name?

    If the chart objects are on a chart sheet, try:
    For Each cht In Sheets(strChart).ChartObjects

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    If you create a zip file from the workbook, that should be small enough to attach to a post.

  12. #12
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    Thanks Hans,

    I hadn't even thought of that (duh!!! -- is it Friday, yet?!?). The zipped workbook file is attached. It includes the macro you gave me.

    This is a sample workbook as opposed to my actual workbook. I have had to remove or replace any private or identifying information and mock-up sample numbers to "protect the innocent" (and my job). I also shrank the file down by deleting several of the charts within the chart sheet. Although the formulas are protected (locked cells), there is no password on them, so you can easily unprotect the sheets.

    Hope this helps explain what I have done in these spreadsheets and charts.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    This is a situation I hadn't thought of - separate charts on a chart sheet with a blank chart (apart from the title and legend). Try this (corresponding to Steve's suggestion):

    Sub AdjustSource()
    Const strSource = "2004 Data"
    Const strTarget = "2005 Data"
    Const strChart = "2005 Charts"

    Dim cht As ChartObject
    Dim ser As Series

    Sheets(strChart).Unprotect

    For Each cht In Sheets(strChart).ChartObjects
    For Each ser In cht.Chart.SeriesCollection
    ser.Formula = Replace(ser.Formula, strSource, strTarget)
    Next ser
    Next cht

    Sheets(strChart).Protect

    Set ser = Nothing
    Set cht = Nothing
    End Sub

  14. #14
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Chart Search & Replace (2002 SP-2)

    Hans,

    Thank you. It worked perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •