Rudoublesedoublel
Potato korv
   
  
    
   
Since: 2.1.02
From: Kentucky - Home of the 8 time NCAA Champ Wildcats
Since last post: 32 days
Last activity: 22 hours
|  #1 Posted on 11.1.05 1720.52 | Instant Rating: 5.00 | I'm working on some reports at work and am hoping that there is an easier solution to my problem than the one that I've found.
I am using an Excel workbook with 12 worksheets (tabs) - I have one tab for each month of the year. I have 11 graphs on each tab. In order to speed up my production time, I created the graphs (and data layout-which are all links to separate spreadsheets) for my "January" tab and worked with it until I had what I wanted. I then copy the completed tab and paste it to all of the other tabs. It is easy enough for me to do a "find and replace" to change the reference codes for the cells with links, but here is what I'm having a problem with: All of the graphs on every tab refer to my "January" tab. The find and replace won't make them refer to the proper tab. I can change the tab reference by clicking on the graph, selecting Source Data from the drop-down menu, and manually changing the tab reference for each data range. Thus my question - is there an easier way to copy a bunch of data and graphs from tab a paste them in tab b and have the graphs in tab b look to tab b for their values instead of tab a? OR - Am I just going to have to manually change this in every graph on every bleeping tab?
Google hasn't been kind in my search for this - many thanks for any suggestions.| Promote this thread! |           | HairRaiser
Kishke
Since: 13.1.03
From: S. Attleboro, MA
Since last post: 2091 days
Last activity: 1426 days
| #2 Posted on 11.1.05 2324.53 | Instant Rating: 5.00 | If you haven't broken down and done it manually by then, I can probably write you a bit of VBA to do it at work on Tuesday, although I may not be able to get it PM'd to you until I get back home.
From the sounds of it, the source data tabs are all set up identically, so all that needs to be updated is the tab name, which is based on the month, right?
MSDN used to have easy to navigate Excel object model documentation but they've redesigned the pages (at least the ones I'm looking at) and I'm having trouble doing this from memory. :-)
I did write something a few years back that found all the external links and wrote out the locations to a text file. It'll be easy enough to modify that code to just do the replacements.
EDIT: I sent you a ZIP file at the email address in your profile. Good luck!
(edited by HairRaiser on 12.1.05 1719)
March 22, 2004 - Finally the WotD! |
| | |
Thread rated: 5.00