Copy Named Ranges To New Version Of Same Template
Aug 28, 2006
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.
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 a list of cells holding the names of all the ranges that must be copied. The list of named ranges is right below a cell named "Config_values_to_copy", so
range("Config_values_to_copy").offset(1,0).value holds the name of a range (e.g. "Priority_Column") in which case there is a column off values there, that must be copied to a blank instance of a 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
View 9 Replies
ADVERTISEMENT
Apr 27, 2007
I have a workbook with about 25 named ranges that I would like to use in a a different workbook. Is there some way to copy them, or a way to transfer them through code?
View 9 Replies
View Related
Apr 1, 2008
I have the following
wkbDataFile.Worksheets("Admin"). Range("January_Data").Copy Destination:=wb.Worksheets("Admin").Range("January_Data")
In some cases the range "January_Data" won't exist and the macro breaks. Is there a check I can have the macro run through first to ensure the range exists in wkbDataFile.Worksheets("Admin") before going through with the .Copy?
View 3 Replies
View Related
Nov 16, 2009
I have VBA code that copies over several sheets from a workbook, but I'd rather not have all the named ranges come through. Whenever I run the macro it gives me this notice: "A formula or sheet you want to move or copy contains the name 'rngRegion2', which already exists on the destination worksheet..." The destination sheet doesn't originally have this range already, it runs into problems because the sheets I'm copying have rngRegion2 defined on different sheets. I'd like to try and avoid this problem all together by not copying named ranges if possible.
If thats not a viable option, is there code that I can use to tell it to automatically select "Yes - To use the name as defined in the destination sheet" as opposed to prompting the user to select yes/no?
View 2 Replies
View Related
Aug 13, 2013
If I copy worksheet in Excel, the named ranges are copied too. If I copy in VBA, they are not. Is the only solution to loop thru the named ranges or is there something smarter like some "fullcopy"?
View 2 Replies
View Related
Jan 27, 2012
I looking for a macro to look in a list in worksheet "Map_Ref", and take the Range Name of column "A" and copy that range in the corresponding Tab and range as specified in Columns "C" and "D" (i,e. take range name "BB Staff Counts_Tenured" from cell "A2" and copy that range in worksheet "Sheet2" (as specified in cell C2), range "A2" (as specified in cell D2). And so on.
List of worksheet "Map_Ref":
Column AColumn BColumn CColumn DTable/Range Title/Name:
Range in Tab "CMD_1"Copy to Tab:To Range:BB_Staff_Counts_TenuredA126..Q156Sheet2A2Branch_Counts_BBmarketsA35..
C47Sheet2A59Branch_Counts_Chase_SBRM_RegA30..E33
[Code] ........
View 6 Replies
View Related
Dec 20, 2007
Been playing with this for some time and can't quite figure it out. I'm trying to copy a named range and paste special value to another named range of the same size. I recorded a Macro that does what I need, and I'll use if necessary, but thought I'd try to consolidate the code a bit and can't get it to work. The recorded macro is as follows:
Sub PasteRanges()
Application.Goto Reference:="DataCopy30Yr"
Selection.Copy
Application.Goto Reference:="DataPaste30Yr"
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
End Sub
My attempt to consolidate was this:
Sub PasteRanges()
Range("DataCopy30Yr").Copy Destination: Range("DataPaste30Yr").PasteSpecial (xlPasteValues)
End Sub
I get the Run-time error 1004 Copy method of Range class failed when I try to run this. Again, I can use the longer version with no problem, but in the interest of learning and since I have spent some time not being able to come up with the solution, I thought I'd ask the experts opinions before I gave up on it.
View 6 Replies
View Related
Aug 19, 2008
I have a list of invoice #'s on a sheet named "Temp Sheet".
I have a VBA macro that created a new tab for each entry and named it the invoice #. So basically the vba code created a new tab ( based on the number of invoice #'s on my list ), and named each tab an invoice number. So if I had a list of 10 invoice #'s, named S1-S10, the vba code created 10 tabs, named S-1, S-2,. S-3.....
Now to my question.
I have a template sheet I want to copy from ( "Template" ), and select any sheet that starts with "S", and copy/paste this template to.
View 9 Replies
View Related
Apr 28, 2014
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?
View 6 Replies
View Related
Feb 21, 2013
how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
View 8 Replies
View Related
Jun 2, 2006
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
View 2 Replies
View Related
Mar 14, 2013
Merge two columns into one list in excel
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
Here's the formula from the example:
Code:
=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))
I've played around with it, but could not come with any that worked.
View 3 Replies
View Related
Apr 24, 2012
I'm trying to make a macro in Excel 2003 to create x amount of named worksheets that are a copy of a different worksheet.
1. I have a main worksheet that will have a number manually entered into a cell (lets say A1);
2. I have a 'template' worksheet;
3. I'll assign the macro to a button on the main worksheet
If I enter '10' into cell A1 of the main worksheet, I'd like to click the button and have Excel create 10 copies of the template worksheet. These new worksheets should all share the same name with a number after them (ex: banana 1, banana 2..... banana 10).
View 2 Replies
View Related
Sep 15, 2014
I am trying to make a UDF that searches for a header, grabs everything under the header, and pulls it somewhere else. My UDF has three parameters:
1) Output_Range: the named range where the parameters will be pulled to
2) Header: the header to search for in order to copy the data underneath it
3) WorkbookName: the name of the workbook to search in
It looks like this:
VB:
Function LoadParameters(Output_Range As Range, Header, WorkbookName As String)
MyTimer = Timer
'Defining the variables.
Dim HeaderCell, HeaderCellEnd, HeaderRange, Output_Range
[Code]....
View 3 Replies
View Related
Dec 30, 2008
I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10.
I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>.
Sample .......
View 10 Replies
View Related
Jul 17, 2006
How do I go about using named ranges instead of cell names(ie A4:A9)
Private Sub UserForm_Activate()
Dim lngRow As Long
Dim intIndex As Integer
UserForm1.ComboBox1.Clear 'Clear combobox
lngRow = 2 Do While Sheet1.Range("a" & lngRow).Value <> ""
For intIndex = 0 To UserForm1.ComboBox1.ListCount - 1
If UserForm1.ComboBox1.List(intIndex) = Sheet1.Range("a" & lngRow).Value
Then
Goto NextRow
End If
Next intIndex
UserForm1.ComboBox1.AddItem Sheet1.Range("a" & lngRow).Value
NextRow:
lngRow = lngRow + 1
Loop
End Sub
I wanted to change "a" to the defined range "search"?
View 7 Replies
View Related
Dec 5, 2007
the code to add two independent named ranges cell by cell in vba. Both of the named ranges have the same structure and the sum would be posted to a third area of the same structure cell wise.
View 7 Replies
View Related
Jan 28, 2014
At the moment I have to click in the cell and then look at the cells used and look across to the title of the rows.
So for example, performance = D3*D4*D5*D6
I would like, performance = vehicles*availabliity*utilisation*TKM.
That is easy if I have just 1 option. But what if I have 3 options? Naming each cell would be a way to do it but pretty laborious, is there a 'smart' way to use named ranges here?
View 3 Replies
View Related
Feb 12, 2010
I'm creating a KPI spreadsheet which utilizes named ranges to allow for Dynamic charting. I've created the first data input sheet for one of the 10 areas being KPIed. The sheet has 60 named ranges in it.
The goal is to duplicate the existing sheet (Area 1A) 10 times and adjust the named ranges and formulas within the named ranges according to the sheet names.
Is there a way to accomplish this without having to manually recreate or edit every named range for each new sheet?
View 6 Replies
View Related
Jun 16, 2014
I want a sumIF function (based on three criteria) to fill the values of cells in a column so long as there are values in the cells in the column before that one. I was working with trying a loop, but have been shown a faster way is using the With function.
There are four named ranges that all exist on a separate sheet in the file: Crude, Location, Year and Volume. Based on the first three columns of the file I wish to have an output for the sum of volume based on crude, location and year.
Right now, my output simply returns the total sum of the "volume" range in each cell in the output column.
For example, if total sum of values in the volume column is 100 then my output column currently looks like:
100
100
100
100
etc...
Here is my code including the named ranges:
[Code] ......
View 2 Replies
View Related
Jun 20, 2014
I created a simply macro that will adjust the headings of a group of cells as follows.
But if I add a row/column to the sheet, it will then of course attempt to place the headings in the wrong cells.
I have now given that group of cells/range a Name of "AR_Buckets". But I'm not certain how to modify the macro to ensure it always places the information in the correct place.
[Code] ....
View 1 Replies
View Related
Nov 24, 2008
What is the correct syntax if I want to replace two named ranges below to Rng and Rng2 in my code?
View 5 Replies
View Related
Jan 14, 2009
I am having problems getting the code below to follow my intentions. I have 2 files. I have a temporary file that holds all the data that I need. I have a destination file that needs to have data copied into it. In the temp file, I have data for several dates and product types. What I intend to do is to do a vlookup in VBA to look for the date and the product type in the temp file and copy the appropriate data to the destination file. I have numerous named ranges both in my temp file and my destination file. For the code below, I wanted to make a loop to find the date in the temp file that is listed in the destination file. Once this is done, I wanted to find the product type in the temp file that is listed in the destination file. If both conditions exist, then copy a certain range from the temp file to the destination file. My intention is evident in the code, but I don’t think that I am putting in the correct “code format”.
View 9 Replies
View Related
Feb 24, 2009
Macro for two named ranges. I have this code, which works fine...
View 4 Replies
View Related
Apr 29, 2009
(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:
View 6 Replies
View Related
Jul 10, 2009
I'm trying to make my worksheet generate a bunch of named ranges whenever it is activated. However, I'm getting a "Not a valid name" error on the line in red. The value of r is a string, so I really don't see what the issue is. Another time, I got a object defined error, but I think I've declared everyhing too. That line is very similar to the line above, and tends to work once (the first time around the loop). After that, I get the errors I mentioned.
View 4 Replies
View Related
Aug 13, 2009
I have two named ranges, LV1 and LV2 and wish to select them for printing. If the ranges were fixed I would use - Range("A2:H4,A8:H10").Select - how do I do the equivalent using the named ranges in vba?
View 2 Replies
View Related
Aug 25, 2009
The worksheet contains 20 different datasets which are divided into separate 30 x 10 grids, all of which help comprise a larger 300 x 20 grid. I have defined each smaller grid/dataset as its own named range. Originally the entire dataset started in A1, but I am shifting it dataset down by 40 lines to create space to work at the top of my workbook.
I am wanting to create a data validation list in A1, then have cells A3:J32 populate with the named range I select in A1. However I also want this populated dataset at the top of the sheet to be a mirror -- not a copy.
Therefore once I have the data in my range available A3:J32, anything I manipulate would be mirrored in the original range in the lower portion of the workbook.
View 4 Replies
View Related
Dec 29, 2009
I am importing an external Sheet into my Excel workbook but it only imports the data not the named ranges. I need the named ranges to be imported and overwrite any existing range of same name.
Example:
In existing workbook Sheet I have a named range, ABC which is A1:E10 - in the Sheet I am importing the ABC range is A1:E12. So when I have done import I want named range ABC in my workbook to become A1:E12 (as per external Sheet).
This is the code that import data (but not named ranges)
View 11 Replies
View Related
Nov 10, 2008
can I merge 2 named ranges with a build-in function and give this merged range a 3rd name ?
View 4 Replies
View Related
