How to display one chart on multiple sheets

B

bromano

New Member
Joined
May 17, 2010
Messages
17
I have a chart that summarizes monthly data for a variety of topics. I'd like to display the chart on each month's worksheet so I can do a WhatIf and see the dynamic chart change as I change a data entry. I have the chart on a Summary page. I've tried doing a copy and paste of this chart onto each of the monthly pages. It seems to work most of the time ("work" means the column in the chart moves if I change a data entry) but, sometimes the chart doesn't update.

Is there a better way to display a chart on multiple pages? I also tried displaying two worksheets at once...this works, but I'm not sure my viewers can or will want to do that so, I was looking for a easier solution.

Thanks everyone for reading and helping!


//bill
romanow@upstate.edu
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Bill,

Are you wanting a Chart per sheet and it refers to the sheet data or Summary Sheet Data?

Here is a snip of code to create a chart on a sheet. If it is something you can run with then it will need a loop to do on all sheets

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro99()<br>  <SPAN style="color:#00007F">Dim</SPAN> myrange <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Set</SPAN> myrange = Range("B1:F8")<br>  ActiveSheet.Shapes.AddChart.Select<br>  ActiveChart.SetSourceData Source:=myrange<br>  ActiveChart.ChartType = xlColumnClustered<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Click to expand...
 
Upvote 0
Hi Trev... Thanks for the speedy response.

"Are you wanting a Chart per sheet and it refers to the sheet data or Summary Sheet Data?"

Yes, and it would refer to the Summary Sheet Data.

I was looking for a way to attach a spreadsheet here but I don't see a way to do that. I can certainly send the workbook to you if that would help.

//b
 
Upvote 0
No need to send a workbook Bill, the forum doesn't allow uploading, but you can copy and paste spreadsheet data, also possibly convert to HTML.

Here is some sample code to place a chart on all sheets in a workbook and refer the source to a summary sheet range.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro99()<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>ws.Activate<br><br>  <SPAN style="color:#00007F">Dim</SPAN> myrange <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Set</SPAN> myrange = Sheets("Summary").Range("B1:F8")<br>  ActiveSheet.Shapes.AddChart.Select<br>  ActiveChart.SetSourceData Source:=myrange<br>  ActiveChart.ChartType = xlColumnClustered<br><br><SPAN style="color:#00007F">Next</SPAN> ws<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Click to expand...
 
Upvote 0
Much appreciated. Let me work with this and see what I come up with.

Thanks! I'll insert the data chart below.

//Bill :)

<TABLE style="WIDTH: 627pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=2609 border=0><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=481><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=197><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=200><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" span=2 width=197><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=191><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=4 width=191><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=191><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=191><TBODY><TR style="HEIGHT: 56.25pt; mso-height-source: userset" height=234><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 116pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 56.25pt; BACKGROUND-COLOR: transparent" width=481 height=234>ytd - data</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=197></TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=397 colSpan=2>UH Day</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 93pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=388 colSpan=2>UH/VA night</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=382 colSpan=2>Backup
Weekday
</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=382 colSpan=2>Backup
Weekend
</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=382 colSpan=2>Weekend</TD></TR><TR style="HEIGHT: 27pt" height=113><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 27pt; BACKGROUND-COLOR: transparent" height=113>VG</TD><TD class=xl75 style="BORDER-RIGHT: white 1.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>46.5</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>41.5</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>47</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>8</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>9</TD></TR><TR style="HEIGHT: 27.75pt" height=116><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 27.75pt; BACKGROUND-COLOR: transparent" height=116>MC</TD><TD class=xl76 style="BORDER-RIGHT: white 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>54</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>44.5</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>43</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>7</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>9</TD></TR><TR style="HEIGHT: 27pt" height=113><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 27pt; BACKGROUND-COLOR: transparent" height=113>KA</TD><TD class=xl76 style="BORDER-RIGHT: white 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>51.5</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>45.5</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>45</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>17</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b0f0" colSpan=2>32</TD></TR><TR style="HEIGHT: 27pt" height=113><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 27pt; BACKGROUND-COLOR: transparent" height=113>AK</TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b0f0" colSpan=2>18</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b0f0" colSpan=2>41.5</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b0f0" colSpan=2>51</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b0f0" colSpan=2>13</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: white 1.5pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b0f0" colSpan=2>11</TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
How set range for chart in powerpoint created from vba
Replies
0
Views
117
Legacy 519704
L
S
  • Question Question
Display a variety of cells in one cell based on multiple variables
2
Replies
11
Views
157
Peter_SSs
Peter_SSs
E
  • Question Question
Copy data from a Data Entry form's table to the correct date in another tab
Replies
0
Views
342
Exceleration65
E
R
  • Question Question
Formatting data series for chart created via Slicer and Pivot Table
Replies
6
Views
1K
awoohaw
awoohaw
N
  • Question Question
2-step Dynamic Ranges and Charts
Replies
0
Views
1K
Nick Loughton
N

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top