Solved

Displaying a Chart onto another Worksheet

Posted on 2011-09-04
5
255 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 500 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

808 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