Solved

Displaying a Chart onto another Worksheet

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
time format showing wrong 12 51
splitting text of cell to columns 14 24
Manually enter date in datepicker 24 34
Excel sheet question 12 23
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now