Sheet Name

[Mike C]

Hello,

I have a very basic macro to copy a sheet to a new worksheet. This macro is tied to a button which works fine. The problem that I am running into is that the sheet that I am using as my source for the copy also has some code in it. This code is traveling with the copy however it references "Sheet2" and does not update to reference the newly created copy sheet name. I would guess that this is an easy fix, however I haven't been able to find anything online. HELP!

Here is a sample of one line of code from the worksheet that is being copied. I need "Sheet2" to update to the sheet name of the newly created sheet. Or if there is a way to avoid referencing the sheet that might work as well.

If Sheet2.Cells(22, 21).Value = 2 Then

 

[Luke M]

Will the name of the worksheet in XL (not VB) be the same? Options for selecting a worksheet are:

Sheet2.Select 'Use VB name
Sheets(2).Select 'Use sheet position
Worksheets("Sheet1").Select 'Use name as defined in XL

While it is possible to write code the modifies VB code, it can get quite tricky/hairy.

 

[Mike C]

Initially it will be the same name, however once the user types into a certain field in that worksheet, I have included code that renames the worksheet based on the value entered into that specific cell. That is why the VB name would be best.

 

[Luke M]

I would propose then changing the VB name to something more unique like "ImportSheet" so the VBE won't rename it, and you can continue to use:
ImportSheet.Select
or something like that.

 

[kchiba]

Hi Mike,

I have had this problem when the Sheet with the formula actually refers to the Sheet itself

Is Sheet2 the source sheet?, if so in Sheet2 remove all references to Sheet2.

So for instance in Sheet2 if you have =Sheet2!A1+Sheet2!B1, remove Sheet2! and make the formula A1+B1



cheers

kanti