Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 21st 06, 03:51 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 1
Default Copy data in named ranges to a newer version of the same template to identical ranges


Hi All,

Lot of excellent help here - I am almost there - Please help me
connect the dots...

OK - what's the problem: * I will try to keep a version control of a
template I develop on an ongoing basis. The version number I will try
and use to provide an easy way to upgrade any file based on an older
version of the template by simply copying values that reside in cells
that I see on a list of named ranges into a file that is a copy of the
new template.*
- Does this make any sense...

How do I do this - I am not sure - I need help - here are my
thoughts...

My idea of an answer is this:
1: I will use the version control suggested by Tom Ogilvy in
http://www.excelforum.com/showthread.php?t=479156
And keep the version number in a property named "Version".

2: I have a hidden sheet named "config" in which i have an ongoing list
of cells holding the names of all the ranges that must be copied. The
list of named ranges is right below the cell named
"Config_values_to_copy", so
range("Config_values_to_copy").offset(1,0).value could be
"Priority_Column" in which case there is a column off values there,
that must be copied to the new template and placed in the same named
range.

3: I will keep the newest version of the xls-file (template) - not as a
xlt file, just xls for now somewhere - on the LAN in a specific folder
and always have the name of the latest version being: "Risc and issue
documentation Template, latest version.xls" (If possible I will
manually include the version number into the file name instead, and
make sure that there is only one file in the particular folder.) For
this help let's just say that the folder is local
"C:\latest-version-template\" I think that this can somehow be done
based in parts on the answer Ron de Bruin gives to this topic
http://www.excelforum.com/showthread.php?t=564858 - but I am not sure
that it is all there...

4: When a user opens a workbook based on the template a control button
named something like "check for new template version" should be
accessible - alternatively I will hide the code and do this when the
user opens the file.

5: Now here goes: The code must somehow check current version from step
(1) against the version found in the folder with the latest-greatest
version of this template. If the template available in that folder is
newer than the template that this current file is based upon, then
prompt the user if he wants to upgrade. If YES then I must somehow
iterate throught the named ranges and copy all the values onto a copy
of the new template and afterwards save this intelligently on the users
harddrive.

I imagine using this kind of iteration to go through the named ranges -
this code is altered a bit from elsewhere in the file:

i=1
While Len(Range("Config_values_to_copy").Offset(i, 0).Value) 1
' As long as I am in this loop, then I have values that must be
copied to the new workbook.
[new workbook!] Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value = Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value
i = i + 1
Wend

Can I do this - how do I copy a version of the newer template and paste
the values (by value) into the identical named ranges? Probably the line


[new workbook!] Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value = Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value

needs some re-work. I just cannot see precisely what I need to do.

I look forward to some quality help.

Bye from Denmark

Soren


--
handstand
------------------------------------------------------------------------
handstand's Profile: http://www.excelforum.com/member.php...o&userid=37818
View this thread: http://www.excelforum.com/showthread...hreadid=573788


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
trying to copy a worksheet containing named ranges to anotherworksheet cil9mxm Excel Worksheet Functions 1 December 3rd 08 06:06 PM
How do I copy a group of worksheets with named ranges in Excel 200 sc Excel Worksheet Functions 2 September 26th 06 12:16 AM
copy the valued of named ranges steve Excel Programming 4 August 10th 06 06:31 PM
copy all named ranges in a sheet to seperate sheets Chris Salcedo Excel Programming 8 October 10th 05 06:23 AM
named ranges - changing ranges with month selected gr8guy Excel Programming 2 May 28th 04 04:50 AM


All times are GMT +1. The time now is 12:04 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017