Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoreference Code
Hi All,
I have a sheet with a code. I would like to make multiple copies of this sheet, Is there a way to make the code change references automatically? for example: I have a sheet with table in the range(A1:P1) and I have vba code which performs mathematical calculations and gives me the result in another table in the same sheet. All tables have names and vba code uses range names to identify position and value. When I make a copy of this sheet, the code still refers to the range in sheet1 Is there a way to change all references to local sheet. I hope my explanation is clear Can anyone help me? Thanks vishnu |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoreference Code
Hi Vishnu,
If you copy a sheet with a named range that named range is created in the copied sheet with the sheet name as a prefix. nr1 = 'Sheet1'!Range("A1:A10") becomes 'Sheet1 (2)'nr1 = 'Sheet1 (2)'!Range("A1:A10") and if you rename the sheet MySheet!nr1 = MySheetnr1 = MySheet!Range("A1:A10") Youwont see this in Excel's built in dialog box but if you use a free add-in like NameManager by Jan Karel Patriese it will be displayed. HTH Regards Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoreference Code
Hi Michael, Thanks for your reply. My mistake, I did not give you the correct definition of what I am stuck at. This is a full description of what I have, Sheet(rawdata) ( this is my source sheet, the data in this sheet does not change it is the source from where everything is pulled out) Sheet1 Listbox 1 (This has unique values from one of the columns(D) from rawdata) Listbox 2 ((This has unique values from one of the columns(C) from rawdata) Listbox 3 (This has unique values from one of the columns(F) from rawdata) User chooses a value from either of the listboxes and selects one value at a time. Table ! is populated with user choices and the user inputs some attributes for values chosen from the listboxes. Table2 is copy of table 1 with blank rows deleted and unique values from column2 of table1 are also found out. table3 matches values in table 2 and sheet(rawdata) and finds product of certain cells I would like to create copies of this sheet and use them and still have all the functionality. When I made a copy this sheet, the vba code still corresponds to the original sheet. Can I make this sheet into a template(is that possible) where in I can create as many copies of this sheet in the same workbook, and I want and all functions and code to perform I hope my explanation is clear Any suggestions? Thanks vishnu On Mar 29, 4:28*pm, "michael.beckinsale" wrote: Hi Vishnu, If you copy a sheet with a named range that named range is created in the copied sheet with the sheet name as a prefix. nr1 = 'Sheet1'!Range("A1:A10") becomes 'Sheet1 (2)'nr1 = 'Sheet1 (2)'!Range("A1:A10") and if you rename the sheet MySheet!nr1 = MySheetnr1 = MySheet!Range("A1:A10") Youwont see this in Excel's built in dialog box but if you use a free add-in like NameManager by Jan Karel Patriese it will be displayed. HTH Regards Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |