I have several macros that reference tabs in a workbook. When the file is
translated to Chinese / Korean, the tab names on the worksheets are also
translated. So when the macro references these tabs, it can't find them. I
have tried copying the Chinese / Korean and substituting into the macro, but
I can't seem to copy the characters. Is there an add-in (for double byte
characters) that I need in order to be able to copy and paste the characters
into the macro so that I can get it to work properly?

Thanks in advance for any suggestions.

R,

Instead of using the tab name, try using the worksheet's codename. Look at the sheet within the
project explorer, and look for something like

Sheet1 (Sheet1)

The value is the ()'s is the sheetname, which will change as the sheet is renamed:

Sheet1 (NewSheetName)

You can either use

Worksheets("NewSheetName").Range(.....)

or

Sheet1.Range(....)

I'm not sure if the sheet codename will change when the language changes, but it is worth a try.

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
I have several macros that reference tabs in a workbook. When the file is
translated to Chinese / Korean, the tab names on the worksheets are also
translated. So when the macro references these tabs, it can't find them. I
have tried copying the Chinese / Korean and substituting into the macro, but
I can't seem to copy the characters. Is there an add-in (for double byte
characters) that I need in order to be able to copy and paste the characters
into the macro so that I can get it to work properly?

Thanks in advance for any suggestions.

Bernie .. thanks .. OK not sure if I understand your suggestion. Here is a
sample of what I have:

Sheets("Data Entry Page").Select
Sheets("Data Entry Page").Unprotect Password:="password"

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Data Entry Page is the name of the tab. Normally when translated I could
copy the new name of the sheet and paste it into the macro .. and I assume it
would work. However, the double byte characters (Chinese) I can't seem to
copy.

So as in the above example:

Sheets("Data Entry Page").Select

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

Same in the second example:
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Use "=Sheet2!R22C6"?

Thanks

"Bernie Deitrick" wrote:

R,

Instead of using the tab name, try using the worksheet's codename. Look at the sheet within the
project explorer, and look for something like

Sheet1 (Sheet1)

The value is the ()'s is the sheetname, which will change as the sheet is renamed:

Sheet1 (NewSheetName)

You can either use

Worksheets("NewSheetName").Range(.....)

or

Sheet1.Range(....)

I'm not sure if the sheet codename will change when the language changes, but it is worth a try.

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
I have several macros that reference tabs in a workbook. When the file is
translated to Chinese / Korean, the tab names on the worksheets are also
translated. So when the macro references these tabs, it can't find them. I
have tried copying the Chinese / Korean and substituting into the macro, but
I can't seem to copy the characters. Is there an add-in (for double byte
characters) that I need in order to be able to copy and paste the characters
into the macro so that I can get it to work properly?

Thanks in advance for any suggestions.

RBeau,

For example,

Sheet1.Select
Sheet1.Unprotect Password:="password"
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='" & Sheet1.Name & "'!R22C7"


Note that you can control the codename - select the worksheet in the Project Explorer window, then
in the Properties window (press F4 to show it if it isn't visible) change the name property of the
sheet to something that makes sense: shtDataEntryPage as an example, in which case your code
would be

shtDataEntryPage.Select
shtDataEntryPage.Unprotect Password:="password"
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='" & shtDataEntryPage.Name & "'!R22C7"

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

No. Worksheets(3) would be the third worksheet, Sheets(3) would be the third tab (only important if
you have chart sheets).

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
Bernie .. thanks .. OK not sure if I understand your suggestion. Here is a
sample of what I have:

Sheets("Data Entry Page").Select
Sheets("Data Entry Page").Unprotect Password:="password"

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Data Entry Page is the name of the tab. Normally when translated I could
copy the new name of the sheet and paste it into the macro .. and I assume it
would work. However, the double byte characters (Chinese) I can't seem to
copy.

So as in the above example:

Sheets("Data Entry Page").Select

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

Same in the second example:
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Use "=Sheet2!R22C6"?

Thanks

"Bernie Deitrick" wrote:

R,

Instead of using the tab name, try using the worksheet's codename. Look at the sheet within the
project explorer, and look for something like

Sheet1 (Sheet1)

The value is the ()'s is the sheetname, which will change as the sheet is renamed:

Sheet1 (NewSheetName)

You can either use

Worksheets("NewSheetName").Range(.....)

or

Sheet1.Range(....)

I'm not sure if the sheet codename will change when the language changes, but it is worth a try.

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
I have several macros that reference tabs in a workbook. When the file is
translated to Chinese / Korean, the tab names on the worksheets are also
translated. So when the macro references these tabs, it can't find them. I
have tried copying the Chinese / Korean and substituting into the macro, but
I can't seem to copy the characters. Is there an add-in (for double byte
characters) that I need in order to be able to copy and paste the characters
into the macro so that I can get it to work properly?

Thanks in advance for any suggestions.

Bernie .. thanks again ... OK the Sheet1.Select works fine .... it will find
the correct worksheet and make the changes. The only change that I still am
having trouble with is the line:

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Here.. when I change the property name it then deletes the Chinese name on
the tab in the worksheet, so I can't do that. Can you suggest the syntax that
could be used if I was to use again the Sheet# here?

For instance:

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Sheet1'!R22C6"

This of course doesn't work ...

Thanks again ... !




"Bernie Deitrick" wrote:

RBeau,

For example,

Sheet1.Select
Sheet1.Unprotect Password:="password"
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='" & Sheet1.Name & "'!R22C7"


Note that you can control the codename - select the worksheet in the Project Explorer window, then
in the Properties window (press F4 to show it if it isn't visible) change the name property of the
sheet to something that makes sense: shtDataEntryPage as an example, in which case your code
would be

shtDataEntryPage.Select
shtDataEntryPage.Unprotect Password:="password"
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='" & shtDataEntryPage.Name & "'!R22C7"

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

No. Worksheets(3) would be the third worksheet, Sheets(3) would be the third tab (only important if
you have chart sheets).

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
Bernie .. thanks .. OK not sure if I understand your suggestion. Here is a
sample of what I have:

Sheets("Data Entry Page").Select
Sheets("Data Entry Page").Unprotect Password:="password"

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Data Entry Page is the name of the tab. Normally when translated I could
copy the new name of the sheet and paste it into the macro .. and I assume it
would work. However, the double byte characters (Chinese) I can't seem to
copy.

So as in the above example:

Sheets("Data Entry Page").Select

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

Same in the second example:
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Use "=Sheet2!R22C6"?

Thanks

"Bernie Deitrick" wrote:

R,

Instead of using the tab name, try using the worksheet's codename. Look at the sheet within the
project explorer, and look for something like

Sheet1 (Sheet1)

The value is the ()'s is the sheetname, which will change as the sheet is renamed:

Sheet1 (NewSheetName)

You can either use

Worksheets("NewSheetName").Range(.....)

or

Sheet1.Range(....)

I'm not sure if the sheet codename will change when the language changes, but it is worth a try.

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
I have several macros that reference tabs in a workbook. When the file is
translated to Chinese / Korean, the tab names on the worksheets are also
translated. So when the macro references these tabs, it can't find them. I
have tried copying the Chinese / Korean and substituting into the macro, but
I can't seem to copy the characters. Is there an add-in (for double byte
characters) that I need in order to be able to copy and paste the characters
into the macro so that I can get it to work properly?

Thanks in advance for any suggestions.

R,

I'm a little confused - possibly because it's Monday. Did you try

RefersToR1C1:= _
"='" & Sheet1.Name & "'!R22C6"

in place of:

RefersToR1C1:= _
"='Data Entry Page'!R22C6"

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
Bernie .. thanks again ... OK the Sheet1.Select works fine .... it will find
the correct worksheet and make the changes. The only change that I still am
having trouble with is the line:

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Here.. when I change the property name it then deletes the Chinese name on
the tab in the worksheet, so I can't do that. Can you suggest the syntax that
could be used if I was to use again the Sheet# here?

For instance:

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Sheet1'!R22C6"

This of course doesn't work ...

Thanks again ... !




"Bernie Deitrick" wrote:

RBeau,

For example,

Sheet1.Select
Sheet1.Unprotect Password:="password"
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='" & Sheet1.Name & "'!R22C7"


Note that you can control the codename - select the worksheet in the Project Explorer window,
then
in the Properties window (press F4 to show it if it isn't visible) change the name property of
the
sheet to something that makes sense: shtDataEntryPage as an example, in which case your code
would be

shtDataEntryPage.Select
shtDataEntryPage.Unprotect Password:="password"
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='" & shtDataEntryPage.Name & "'!R22C7"

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

No. Worksheets(3) would be the third worksheet, Sheets(3) would be the third tab (only important
if
you have chart sheets).

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
Bernie .. thanks .. OK not sure if I understand your suggestion. Here is a
sample of what I have:

Sheets("Data Entry Page").Select
Sheets("Data Entry Page").Unprotect Password:="password"

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Data Entry Page is the name of the tab. Normally when translated I could
copy the new name of the sheet and paste it into the macro .. and I assume it
would work. However, the double byte characters (Chinese) I can't seem to
copy.

So as in the above example:

Sheets("Data Entry Page").Select

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

Same in the second example:
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Use "=Sheet2!R22C6"?

Thanks

"Bernie Deitrick" wrote:

R,

Instead of using the tab name, try using the worksheet's codename. Look at the sheet within
the
project explorer, and look for something like

Sheet1 (Sheet1)

The value is the ()'s is the sheetname, which will change as the sheet is renamed:

Sheet1 (NewSheetName)

You can either use

Worksheets("NewSheetName").Range(.....)

or

Sheet1.Range(....)

I'm not sure if the sheet codename will change when the language changes, but it is worth a
try.

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
I have several macros that reference tabs in a workbook. When the file is
translated to Chinese / Korean, the tab names on the worksheets are also
translated. So when the macro references these tabs, it can't find them. I
have tried copying the Chinese / Korean and substituting into the macro, but
I can't seem to copy the characters. Is there an add-in (for double byte
characters) that I need in order to be able to copy and paste the characters
into the macro so that I can get it to work properly?

Thanks in advance for any suggestions.

Bernie .... thanks alot ... that works. Not sure what was wrong before but
that provides the ability to reference the corrent sheet and the correct cell
without referring to the actual name .. exactly what I was trying to do.

Many thanks for your help on this!

"Bernie Deitrick" wrote:

R,

I'm a little confused - possibly because it's Monday. Did you try

RefersToR1C1:= _
"='" & Sheet1.Name & "'!R22C6"

in place of:

RefersToR1C1:= _
"='Data Entry Page'!R22C6"

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
Bernie .. thanks again ... OK the Sheet1.Select works fine .... it will find
the correct worksheet and make the changes. The only change that I still am
having trouble with is the line:

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Here.. when I change the property name it then deletes the Chinese name on
the tab in the worksheet, so I can't do that. Can you suggest the syntax that
could be used if I was to use again the Sheet# here?

For instance:

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Sheet1'!R22C6"

This of course doesn't work ...

Thanks again ... !




"Bernie Deitrick" wrote:

RBeau,

For example,

Sheet1.Select
Sheet1.Unprotect Password:="password"
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='" & Sheet1.Name & "'!R22C7"


Note that you can control the codename - select the worksheet in the Project Explorer window,
then
in the Properties window (press F4 to show it if it isn't visible) change the name property of
the
sheet to something that makes sense: shtDataEntryPage as an example, in which case your code
would be

shtDataEntryPage.Select
shtDataEntryPage.Unprotect Password:="password"
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='" & shtDataEntryPage.Name & "'!R22C7"

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

No. Worksheets(3) would be the third worksheet, Sheets(3) would be the third tab (only important
if
you have chart sheets).

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
Bernie .. thanks .. OK not sure if I understand your suggestion. Here is a
sample of what I have:

Sheets("Data Entry Page").Select
Sheets("Data Entry Page").Unprotect Password:="password"

ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Data Entry Page is the name of the tab. Normally when translated I could
copy the new name of the sheet and paste it into the macro .. and I assume it
would work. However, the double byte characters (Chinese) I can't seem to
copy.

So as in the above example:

Sheets("Data Entry Page").Select

I can use the syntax Sheet3.Select and it will select instead the third tab
in the worksheet?

Same in the second example:
ActiveWorkbook.Names.Add Name:="Conversion_Rate", RefersToR1C1:= _
"='Data Entry Page'!R22C6"

Use "=Sheet2!R22C6"?

Thanks

"Bernie Deitrick" wrote:

R,

Instead of using the tab name, try using the worksheet's codename. Look at the sheet within
the
project explorer, and look for something like

Sheet1 (Sheet1)

The value is the ()'s is the sheetname, which will change as the sheet is renamed:

Sheet1 (NewSheetName)

You can either use

Worksheets("NewSheetName").Range(.....)

or

Sheet1.Range(....)

I'm not sure if the sheet codename will change when the language changes, but it is worth a
try.

HTH,
Bernie
MS Excel MVP


"RBeau" wrote in message
...
I have several macros that reference tabs in a workbook. When the file is
translated to Chinese / Korean, the tab names on the worksheets are also
translated. So when the macro references these tabs, it can't find them. I
have tried copying the Chinese / Korean and substituting into the macro, but
I can't seem to copy the characters. Is there an add-in (for double byte
characters) that I need in order to be able to copy and paste the characters
into the macro so that I can get it to work properly?

Thanks in advance for any suggestions.