VB code to control Workbook2 form Workbook1

[rjm56]

Hello,
I am hoping someone can help me please.


I am using code associated with Workbook1 to control Workbook2.

Workbook1 = Bank Transactions1.xlsm
Workbook2 = Bank Transactions2.xlsm

These lines of code works :

Workbooks("Bank Transactions2.xlsm").Worksheets(2).Activate
Workbooks("Bank Transactions2.xlsm").Worksheets("Transactions").Activate

But this (which is what I wish to use) does NOT work:

Workbooks("Bank Transactions2.xlsm").Worksheets("Sheet11").Activate


Has anyone any idea why not ?

Notes:
1) Office 2013
2) In a Private Sub CommandButton14_Click()
3) Sheet11 is the "Transactions" tab, and corresponds to the 2nd worksheet along the tabs .....hence Worksheets(2)

Thanks
John

[IonutC]

Hi,

Put your excel file so we can help. What do you want to do, after that sheet is activated?

[Norie]

John

If Sheet11 is the codename for the 'Transactions' tab you can't use it like this to refer to the worksheet.

[rjm56]

John

If Sheet11 is the codename for the 'Transactions' tab you can't use it like this to refer to the worksheet.

Yes ............sheet11 is the (Name) in the properties ............and Transactions is the Name in the properties.

I cannot post a jpeg image..............but I am sure you understand what I mean.

How can I use it please ...........using the sheet11 reference ?

Thank you for your help.

[rjm56]

John

If Sheet11 is the codename for the 'Transactions' tab you can't use it like this to refer to the worksheet.

Yes ............sheet11 is the (Name) in the properties ............and Transactions is the Name in the properties.

I cannot post a jpeg image..............but I am sure you understand what I mean.

How can I use it please ...........using the sheet11 reference ?

Thank you for your help.

[Norie]

John

Is either workbook the workbook the code is in?

Why do you want to use the codename instead of the tab name?

[rjm56]

Hi,

The code is in Workbook1 and it controls Workbook2.

Tab names can be changed very easily................The codename, or what I call the sheet name (sheet11) can be changed but people are less likely to change it.

[Norie]

John

You can't refer directly to a worksheet using its codename unless you programatically access the VBE and even then things are a bit 'indirect'

To do that it would look something like this.

Please Login or Register  to view this content.

An alternative would be something like this.

Please Login or Register  to view this content.

That code is a bit simpler but if you had a lot of worksheets looping might slow things down a bit.

[rjm56]

That is very kind of you Norie, thank you

John