Solved

Displaying a Chart onto another Worksheet

Posted on 2011-09-04
5
256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

751 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