+ Reply to Thread
Results 1 to 7 of 7

Copying and Pasting Tables and Graphs.

  1. #1
    Registered User
    Join Date
    02-05-2017
    Location
    Rockhampton, Australia
    MS-Off Ver
    2010
    Posts
    5

    Post 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.
    Attached Files Attached Files
    Last edited by TWG; 02-08-2017 at 08:20 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,076

    Re: Copying and Pasting Tables and Graphs.

    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.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    02-05-2017
    Location
    Rockhampton, Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Copying and Pasting Tables and Graphs.

    Thankyou for you reply dflak I have uploaded a version without any recorded data. You should be able to see how the macro works and the issue I am having with the graphs.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,076

    Re: Copying and Pasting Tables and Graphs.

    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.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,076

    Re: Copying and Pasting Tables and Graphs.

    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.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-05-2017
    Location
    Rockhampton, Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Copying and Pasting Tables and Graphs.

    Hi dflak,

    Thankyou for your work however I don’t believe I explained very well what I was trying to achieve and how my current macro's work, I have noticed some of the old macro's were still listed in the spreadsheet and do apologise for this and have removed them and any irrelevant information / data. So I'll try again and take my time in explaining this and to ensure I upload another workbook only containing the relevant information, to minimise your work. Also being in Australia we have quite a time difference, creating delays in our response so I’ll try and be very informative.


    What I currently have;

    So currently on the sheet named ‘Title Page’, you have 3 buttons with 3 macros linked to each. Button one being 'New EX Tracker Sheet' linked to macro 'New_EX_Sheet', then 'New DL Tracker Sheet' linked to macro 'New_DL_Sheet' and finally 'New DIP Tracker Sheet' linked to macro 'New_DIP_Sheet'.

    When you click on 'New Ex Tracker Sheet' the macro 'New_EX_Sheet' is run. This macro copies the table labelled Excavator Targets and its 3 adjacent graphs found on Sheet 1. It then returns to the EX Tracker page and inserts then above the existing tables and graphs, feel free to try this for each of them to gain a better idea of what’s going on.
    From there someone on my team will then go to this new Table and update the information; such as bucket type, arrival date, target dates and etc... The issue that then occurs is that the adjacent graphs to this newly inserted table are still linked to the original table found below it. So the data entered is not reflected in the adjacent graphs. Thus, we end up with every graph on this sheet being linked to the one original table.

    My short term solution to this can be found on the spreadsheet named 'Formatting' where the correct links to the data for each of the graphs can be found. Each time a new sheet is entered each graph must have these links copied and pasted into them to link them to the correct table (adjacent table). From then on it is fine and works fine and dandy; however, this does need to be done each time a new sheet is added via the macro’s I have written. This is however, proving very ineffective as many of the people using this spreadsheet have limited knowledge of excel and do not manage this correctly and simply end up corrupting the graphs.


    The Aim;

    What I am hoping to do, is find a means of having of having the newly inserted graphs update its links when its copied from 'Sheet1' and inserted via the macro into its respective sheet, either 'EX Tracker', 'DL Tracker', and 'DIP Tracker'. These are the sheets my team will work out of.

    To achieve this I am unsure if I could change the formula's used to link the graphs to its data, or if a macro to edit the newly inserted graphs formulas could be written simulating the process I do now cutting pasting them from the 'Formatting' sheet, however, removing the high chance of human error. I have tried both of these and failed miserably with my limited skills in excel.

    Any help at this point would be greatly appreciated!


    Cheers, TWG.

  7. #7
    Registered User
    Join Date
    02-05-2017
    Location
    Rockhampton, Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Copying and Pasting Tables and Graphs.

    Please note I have uploaded the new workbook as 'Master Tracker - 09.02.17'.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pasting Graphs Into powerpoint, HELP!
    By itshere in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-12-2015, 08:18 AM
  2. Replies: 3
    Last Post: 12-10-2014, 10:38 AM
  3. Copying Pivot Tables & Graphs to Duplicate Locations
    By Davek81 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-22-2014, 02:43 AM
  4. Copying & Pasting Values from Pivot Tables As Array
    By Mr Denove in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2013, 04:31 AM
  5. [SOLVED] Copying and pasting data tables with changing row counts
    By arleutwyler in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2013, 11:14 AM
  6. Copying and pasting graphs
    By irelaj2 in forum Excel General
    Replies: 3
    Last Post: 02-16-2009, 03:49 PM
  7. pasting excel graphs into word
    By mufan in forum Excel General
    Replies: 2
    Last Post: 07-09-2008, 10:41 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1