Merge (consolidate) data from multiple workbooks with values derived from formulas

[Hester's Dad]

(Excel 2007)

Hello again,

I'm trying to figure out a way to merge the data generated at three different versions of the same workbook (running on three different workstations) into a single workbook, but I'm running into trouble, apparently because the data in each workbook is derived from formulas, rather than being plain, manually entered numbers. An example of the data entry workbook (used for tallying statistics) and the current consolidating workbook are attached.

The workbook "Copy of Desk Tally Robot" tabulates statistics on the worksheet "Results Sheet" based on control button clicks on the worksheet "Input Sheet." The "Save data to Shared Drive" button on "Input Sheet" makes a copy of "Results Sheet" and saves that copy to a folder on a network drive -- the folder and file name derived from NOW() in mmmm and mmmm, dd, yyyy formats respectively, with unique prefixes for each workstation. The code for the save function, since I think it might have something to do with my trouble, is:

Please Login or Register  to view this content.

The "CONSOLIDATOR" workbook also uses a control button which prompts for the workbooks to be merged (this part works) and then places each in its own worksheet (ideally I'd like it to merge the data into a single worksheet automatically, but getting the three worksheets and consolidating them via the Data tab is acceptable for now). Unfortunately, instead of giving me the data from the three worksheets, I get three new worksheets, each with data from the same workbook. The code for the Consolidator is:

Please Login or Register  to view this content.

I notice that when the Results Sheets are saved, individual cells are displaying the values at their last save, but if you click into them, you get a formula referring back to the Input Sheet of the robot on the workstation where data was originally input. Is there a way to either save the Results Sheets as plain values, without referring back, or consolidate them based on the display values, and not the formulae? Any advice would be greatly appreciated, and if I've been unclear or left out any useful information, please to let me know.

Thanks,

-Hester's Dad

[Hester's Dad]

OK. After further investigation and experimentation, it looks like what I needed was a paste special to capture the values instead of the formulae. Inserting the following code:

Please Login or Register  to view this content.

after the line

Please Login or Register  to view this content.

has done the trick.

-HD

[Hester's Dad]

Scratch the above. The inserted code does paste values, but for some reason it's pasting them into all previously saved worksheet copies for the desk the workbook pertains to. In other words, when I click the "save to shared drive" button, it creates a copy of the Results Sheet and saves it with values and today's date for a file name, but it's also pasting the values all results sheet copies, even those with file names from yesterday, the day before, &c. No idea why this would be the case, or how to remedy it, but I'll continue to puzzle it over, and if anyone can shed some light, I'd be most grateful. Luckily, this is all in the developmental/experimental phase, or I would have lost a whole week's worth of data.

-HD

[pike]

Hey Hester's Dad

jb has complied some good example here
https://sites.google.com/a/madrocket...ssistant/files

you can use the copy destination syntax

can you zip up the files so I can have a look? - Wireless broad band here is a pain and practical stops

[Hester's Dad]

Hi Pike,

I'll take a look at the link you posted and see whether there's something that works for me there. What I've currently seems to run, but it's real rough, so I'd love to make improvements.

As far as the save issue I mentioned above goes, I stripped the new code, then looking at it, thought, "it can't possibly be doing what it seems like it's doing," so I re-inserted it, and it's working fine now. I must have gotten confused about something; I have a test bed on my office machine that runs on and saves to my hard drive, and when that works I amend and move to a second set of machines that run off the hard drive, but save to a shared drive. I must have lost something in the translation.

Anyhow, here are the files, zipped this time. CONSOLIDATOR is updated slightly from the iteration attached previously, as I've been continuing to work on it.

Thanks,

-HD

[pike]

Hey Hester's dad

you could change a few things

utilise "ScreenUpdating" to stop the flicker

Please Login or Register  to view this content.

use the "case" syntax is stead of all the "if"s

Please Login or Register  to view this content.

Avoid "select"ing the range by using the "With" structure

Please Login or Register  to view this content.

changed to

Please Login or Register  to view this content.

Romper and co have a do's and dont's in the tips forum

[Hester's Dad]

Thanks, Pike, for the advice. I'd stumbled across screenupdating while reading some other posts, and have started working it in. I'll definitely take a look at & try your other suggestions; I'm learning as I go, so grabbing the first thing that comes close to working for me, and sticking with it. I've suspected there were better ways to go about things, but well. An educational project, to be sure. Thanks again for taking the time to look things over.

-HD

[Hester's Dad]

To follow up:

I did turn off screenupdating. It's much nicer without the flicker.

I changed from 'Select' to 'With' in the Consolidator, and it works great.

After studying it a while, I see the point of using the 'Case' syntax rather than all the 'If' statements in the Tally Robot, but since that aspect of the project is not giving me any problems as it is, I did not actually change it. I appreciate the suggestion, and if I ever rebuild the project, I'll give 'Case' a try.

Bottom line, the two workbooks are now functioning; the Tally Robot like a dream, and the Consolidator adequately, if not perfectly. While there are some fixes I'd like to make, the original question I had has been answered, and then some. I'm going to consider this one solved.

Thanks again,

-HD