Solved

Displaying a Chart onto another Worksheet

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

617 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