Solved: copy worksheet with referencing old worksheet

[av8tordude]

I have this code that copies a worksheet and renames it. Is it possible to copy the sheet without referencing the old sheet? When i run code on the new worksheet, it references the old worksheet.

Dim MySheetName As String

MySheetName = cboYear
Sheets("Master").Copy before:=Sheets("f2106")
ActiveSheet.Name = MySheetName

[mancubus]

perhaps


ActiveSheet.Copy

[av8tordude]

Let me try asking it differently, when I copy a sheet1, thus creating sheet2, then names in the name manager referencing sheet1 instead of referencing sheet2.

i.e.
sheet1:
=OFFSET('2010'!$T$12,0,0,COUNTA('2010'!$T$12:$T$5000),6)

sheet2
=OFFSET(Master!$T$12,0,0,COUNTA(Master!$T$12:$T$5000),6)


So my question is it possible to create a duplicate copy of sheet1 without the name manager referencing sheet1?

[BrianMH]

Do you want to copy just the values or do the formulas need to follow as well?

[av8tordude]

I need everything that is part of the sheet (i.e. Named Ranges, formulas, Hyperlinks, buttons to run specific codes., etc.), except, I don't want the copied sheet to reference the old sheet.

If possible, it should behave like I created it from start.

[BrianMH]

i.e.
sheet1:
=OFFSET('2010'!$T$12,0,0,COUNTA('2010'!$T$12:$T$5000),6)

Is sheet1 called 2010 or does this formula refer to another sheet?

[av8tordude]

The code in my original post will copy the original sheet and re-namesthe sheet base on the user's input in the combo box (cboYear). So, the code you create must have the flexiblity to be incorporated with my code in my original post.

The original sheet is named "Master". The example I provided was the copied sheet.

[BrianMH]

What I am trying to understand is whether the formulas on the master sheet that you copy from refer to the master sheet or another sheet. If it refers to itself this is redundant and why when a sheet is copied it still refers to the original. If you remove those self referals then when it is copied it will no longer point to the original.

You may want to post your workbook so we can look at what you need.

Thanks

[av8tordude]

Unfortunately, the workbook is to big to post, but I understand what you mean. The formulas in the sheets are OK. Its the named ranges and the to codes in the sheet that are the problem.

[BrianMH]

Well I just tested this myself. I created a sheet with named ranges and refered to these with a formula. Then I copied the sheet using the same method as your using and it worked fine and the new sheets named ranges and formulas refer to themselves. So not sure whats going on with yours. Can you maybe just save your master sheet as a seperate workbook and upload that?

[av8tordude]

That interesting...I guess it's working now.

What about assigned macro, I get an error (400) when I click on a shape with an assigned macro.

[av8tordude]

I resolve my last issue. I had to move the macros to a module and re-assigned the buttons to those macros.

[BrianMH]

cool