mvnForum Homepage


Print at May 30, 2020 9:12:44 PM

Posted by Quinolin at Feb 2, 2006 2:52:04 PM
Re: The Shop Tool
 
I'm also looking to write a macro that will copy cell data from one worksheet to another to make copying information from your copy of the spreadsheet to the newest version. I don't know much about VBScript, but it seems like there ought to be a way to lookup the value of a cell by cell name (instead of references like A1) and put the value in the current sheet's cell with the same name. If anyone is familiar with VBScript and is interested in making such a macro or at least giving me the basic code and I can extend it to the rest of the tool, let me know via PM.

One way of doing this would be to first name the relevant sections using the "Name" command in the Insert menu. For example, name O12:Q23 on the furniture sheet as "furn_basic" in the old and the new versions, and so on for other input sections. Then using the following VB-script for each of the sections ...

Windows("old_file.xls").Activate
Range("furn_basic").Select
Selection.Copy
Windows("new_file.xls").Activate
Range("furn_basic").Select
ActiveSheet.Paste

Of course, this won't work retroactively as the current version does not have those name-definitions yet. So it would need to be start in an upcoming version, and from the subsequent release forward, it will work.

Doing this would require quite some naming of sections, and when executed it will probably flicker quite a bit as 2 different windows will be activated constantly. But it should work, just not the prettiest solution ever :-)

One neat addition to the script would be to be able to enter the file names for the old and new versions when starting the macro and assigning variables to the names and use those variables in the Windows.Activate command. That way, the macro would work with whatever file name(s). I do have a script somewhere to do that, just would need to find it. Let me know if you would like to do it.
----------------------------------------
Blimb.
Senior Officer of the British Bandits, Midnight Ocean

Puzzle Pirates™ © 2001-2016 Grey Havens, LLC All Rights Reserved.   Terms · Privacy · Affiliates