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

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

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"
<michael.beckins...@ntlworld.com> 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