Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting and Replacing Charts (2003)

    The attached workbook has a consolidated view of charts whose originals are on the subsequent sheets. They are copies of the charts on worksheets Sheet1, Sheet2, Sheet3 and Sheet4. The values displayed on the charts will change when the table values change on Sheet1, Sheet2, Sheet3 and Sheet4; however, if the charts on those worksheets are reformatted in anyway, then copies on the 'Consolidated View' worksheet do not reformat automatically like the original.

    I am trying to delete the 'Consolidated View' charts upon opening the workbook and always paste the most updated version that is manipulated on the Sheet1, Sheet2, Sheet3 and Sheet4 worksheets. I would like to "lock" the order of the worksheets to ensure the charts are pasted down in the proper order as well.

    The code that I used was an attempt to plagarize the Record Macros.

    I don't really like deleting the the range of cells because it leaves a line from the charts. I would also like the Order of the newly pasted charts on the "Consolidated View" worksheet to be Send to Back.

    The variable declaration was an attempt to plagarize code from a previous effort that would select all of the charts on the worksheet as opposed to selecting and deleting a cell range.

    Private Sub Workbook_Open()
    'Dim obj As ChartObject
    'Dim Cht As Chart
    ' For Each obj In Me.ChartObjects
    ' Set Cht = obj.Chart
    Worksheets("Consolidated View").Activate
    Range("B:I").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Insert Shift:=xlToRight
    Sheets("Sheet1").Select
    Charts("Chart 3").Select
    Selection.Copy
    Sheets("Consolidated View").Select
    ActiveSheet.Paste
    Range("B2").Select
    Sheets("Sheet2").Select
    Charts("Chart 1").Select
    Selection.Copy
    Sheets("Consolidated View").Select
    ActiveSheet.Paste
    Range("B10").Select
    Sheets("Sheet3").Select
    Charts("Chart 1").Select
    Selection.Copy
    Sheets("Consolidated View").Select
    ActiveSheet.Paste
    Range("B18").Select
    Sheets("Sheet4").Select
    Charts("Chart 1").Select
    Selection.Copy
    Sheets("Consolidated View").Select
    ActiveSheet.Paste
    Range("B18").Select
    End Sub

    Thanks for your help.
    Amy
    Attached Files Attached Files

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

    Re: Deleting and Replacing Charts (2003)

    Instead of using code, you could do the following:
    - Activate Sheet1.
    - Holding down the Alt key, make the chart cover a range of cells exactly.
    - Select the cells that the chart covers (this is easiest using the keyboard).
    - Select Edit | Copy.
    - Activate the Consolidated View sheet.
    - Hold down the Shift key while selecting the Edit menu.
    - Select Paste Picture Link.
    - You'll have a picture of the chart that is linked to the original, so it will be updated automatically if you modify the original.
    - Repeat for the other charts.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting and Replacing Charts (2003)

    Thank you for the less in Paste Picture Link. It worked well. Now I wish there was a magic format brush that sized charts to a specified range

    Amy

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

    Re: Deleting and Replacing Charts (2003)

    If you hold down the Alt key while moving and resizing a chart (or any object on a worksheet), its edges will be aligned with the nearest cell edge, which makes it much easier to place the chart precisely over a range of cells.

    It's also possible to use VBA code to position and size charts (and other objects).

Posting Permissions

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