Copying and Pasting Tables and Graphs.

Hi.

I'm developing a tracking sheet for work, which is used to monitor the time of rebuilds undertaken upon a machines. Previously it has only been a table with the planned dates of completion and actual dates of completion. However, recently I have created a number of graphs to visually display this information from the tables so that myself and other workers can relate to the information more easily and it is has been acknowledged as critical information for presentation at regular work meetings.

I have been asked to produce a macro so that others can use this tracking sheet with ease. The macro itself is very basic it just copies and pastes in a blank copy of the tables and graphs which are stored on another sheet, above the last completed tracking table.

This issue I am having is the graph references, they are maintaining their reference to the first table produced, thus I have to manual go through and change the data references for each of the charts each time I start a new tracking table. Is there a way to prevent the graph from doing this and just maintaining a cell reference to the table they were pasted in with not the original blank copy stored on another sheet? Please take note that they must all be pasted into the same sheet, I cannot just produce numerous excel spreadsheets.

Thank you in advance for any help on this issue.

It would help a lot if you can provide a couple of sample workbooks without sensitive data so we can observe the issue firsthand. Also what do you mean by copying and pasting a blank copy of tables and graphs?

Do you want the data to be "live" in the target workbook. Or can "pictures" of the chart be used?

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

I think I see what you are doing. You are taking the data grids on Ex Tracker, DL Tracker, Dipper Tracker, and copying them onto a new sheet. You want to put a chart next to each one of these.

Due to the time differences, I don't expect we'll have a lot of overlap in our work days for real time responses, so I'll press on with this assumption unless I hear otherwise.

This is what I did.

First, I removed all merged cells from the three source sheets and the report sheet. Merged cells are bad news for a lot of reasons, but the main one with regard to VBA is that they can seldom be copied. You usually wind up with an error message about the merged cells must be the same size even when they are the same size.

Instead of merged cells, use center across the selection. Type the value you want in the first cell and select the other cells. Then format the cells. One of the formats under horizontal is center across selection. This makes it look like a merged cell, but it acts like a regular cell.

I took your sample sheet and named it Report, and I redefined the charts in terms of the fixed ranges on the Report sheet.

Then in the VB code I clear out the data on the Report sheet, leaving the charts pointing to the now empty cells. Then I copy and paste the three report sheets into their appropriate places on the report sheet. The macro is called copy reports.