Worksheet reference does not work on all computers

zuki

New Member
Joined
Sep 2, 2012
Messages
22
I have the following strange problem:
In a visual basic code i do references like: Workbooks("WorkbookX").Worksheets("WorksheetY").DoSomething
And they work fine. For some other people when running it does not find the worksheet i.e. gives the same error as if I had written
Workbooks("WorkbookX").Worksheets("WorksheetZ").DoSomething
where WorksheetZ does not exist. It helps if I change to the generic naming in excel i.e. Sheet8, Sheet9 but it would be quite cumbersome to change all references.
Does anyone have an idea?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you sure it's the Worksheet reference it's objecting to and not the Workbook one? It's better to supply the extension too:
Rich (BB code):
Workbooks("WorkbookX.xlsx").Worksheets("WorksheetY")
 

zuki

New Member
Joined
Sep 2, 2012
Messages
22
The problem was indeed with the extension! You are a genius! Do you know why it is sometimes necessary to include it?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If a computer has Windows Explorer set to show file extensions, you have to include it in your code. If you include it anyway, it will work regardless of the explorer settings.
 

zuki

New Member
Joined
Sep 2, 2012
Messages
22
Thank you very much! I have no idea how I would have found out if it was not for you :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top