Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Displaying a Chart onto another Worksheet

Posted on 2011-09-04
5
Medium Priority
?
264 Views
Last Modified: 2012-06-27

EE Professionals,

Teylyn wrote a fantastic set of formulas that allow the dynamic production of a Stacked Bar Chart.  I now want to take this chart that is on "Source" worksheet and display it on the "Terget" worksheet.  I will be hiding the Source tab and want it to only be seen in the Terget worksheet.  Attached is the sample.

Much thanks in advance,

B.



Adjusting-Graphic-v1--1-Source-t.xlsm
0
Comment
Question by:Bright01
  • 3
  • 2
5 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 36480714
You can copy and paste the chart with code like the following:
Sub CopyChart()
Worksheets("Source").ChartObjects(1).Copy
Application.Goto Worksheets("Target").Range("D5")   'Top left cell of chart on Target worksheet
ActiveSheet.Paste
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 36480736
If you might run the code repeatedly, you will need to delete the older copies of the chart before pasting the new one. You might also want to avoid changing the active cell and eliminate the flicker that results from the paste operation.

Sub CopyChart()
Dim co As ChartObject
Dim celHome As Range, rgTarget As Range
Application.ScreenUpdating = False
Set celHome = ActiveCell
Set rgTarget = Worksheets("Target").Range("D5")  'Top left cell of chart on Target worksheet
Application.Goto rgTarget
With rgTarget.Worksheet
    For Each co In .ChartObjects
        If co.TopLeftCell.Address = rgTarget.Address Then co.Delete
    Next
    Worksheets("Source").ChartObjects(1).Copy
    .Paste
End With
Application.Goto celHome
End Sub

Open in new window


Brad
0
 

Author Comment

by:Bright01
ID: 36480765
Brad,

Thanks for the quick response.  The chart on the Source is "dynamic"'; meaning that when there is a change to the value, it automatically updates and repositions the chart.  I am looking for the chart on the Target to immediately reflect the changes. With the Macro you have written;

1.) Do I put it in the Worksheet or in a Module?
2.) How do I get it to automatically update the chart as the values in the Source change?
3.) Is there a way to do this without Macros, or is that the most efficient way?

Much thanks,

B.
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 36480859
The code should go in a regular module sheet.

Since the chart on the Target worksheet is a copy of the one on the Source worksheet, it contains the same series formulas and will immediately update whenever the underlying data does so. If you make changes to formatting on the Source sheet, those will not be reflected on the Target sheet unless you rerun the macro.

You can manually (i.e. without code) select the chart on Source worksheet, copy it and paste it on Target worksheet. Like with the macro copy and paste, the copy on Target will update when you change the values on Source worksheet--but won't reflect any formatting changes.

Brad
Adjusting-Graphic-v1--1-Source-t.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 36481204
Brad,

Works great.  Much thanks.  I will be asking a related question that I hope you will answer.

B.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question