One table format, many sheets

[teejayvee]

So I have these workbooks just bursting with worksheets. Many identically formatted worksheets, but differing data in each. I go to sheet #1 and I create a chart that looks all purty and sitch. Then I want to copy that table (in all its formatted glory) to every worksheet, substituting the data in that worksheet into the chart. This requires I select each series and insert the worksheet's name in place of the source worksheet name.

Is there an easier way to do this? It's quite annoying to do it over and over manually, and I feel like I'm missing a simpler solution. I'm using 2010, but a solution in any version would help.

[MarvinP]

Hi tejayvee,

Can you code? You could write a little code to put each successive sheet name into the chart formula if you did.

Application.Worksheets(1).Name in VBA is the name of sheet 1
Application.Worksheets(2).Name is 2nd sheets name.

We could use a sample of your workbook. I'm a little confused in you have a single chart (only on sheet1) and want to use that one chart to display the data on successive sheets.

Click on "Go Advanced" below the message area and then on the PaperClip Icon above the message area to upload a file.

[teejayvee]

Ugh, I'm dumb! Sorry Marvin, I think I misstated my issue. I want to copy and paste the *graph* into each worksheet, preserving it's formatting but using the data in the pasted-into-sheet. So, in the end I want the exact same graph in each sheet, but using the current sheet's data. The problem is that when I copy-paste a graph from worksheet1 into worksheet2, the data plotted is still from worksheet1. I'd rather do something like "paste formula" but for a graph.

I can code in other languages and I'm happy to use VBA if that's the most straightforward way to do what I want to do.

Thanks!

[MarvinP]

Hi,
In trying to understand your problem I've recorded macros to copy the graph to a new sheet. In the Paste options, in 2010 excel there are options: Use Source or Destination formatting. These don't seem to make a difference to the macro code.

There is a object in VBA named "ActiveSheet". You might redifine the charts data source range using VBA and the variable

ActiveSheet.Name

I hope this helps.

[Andy Pope]

This assume the following.

First worksheet contains chart to be copied.
All other worksheets have exactly the same data layout

Please Login or Register  to view this content.