Run-time error '9'

I APOLOGIZE FOR WASTING YOUR TIME.

i BELIEVE I FOUND THE PROBLEM.

Changing the worksheet name without "saving" the excel workbook
does not update the worksheet name that the vba code sees.

If I changed a worksheet name AND saved the workbook, then the
vba code sees the correct worksheet name and the original code worked.

I apologize to you all,
bil





I am getting a Run-time error '9' on line 5 below


Line 2 Dim ptrWrkBk as Workbook
Line 3 Dim ptrWrkSht as Worksheet
Line 4 Set PtrWrkBk = Workbooks(WrkBkName)
Line 5 Set PtrWrkSht = WorkBooks(WrkBkName).Sheets("FieldData")

Code on Line 4 works. I Assume code found workbook.
In Code on line 5 worksheet (sheet1) named "FieldData" exists in the above workbook.
caps and spelling checked

Can anyone see what my problem is?
How do i go about troubleshooting this kind of error?

Also on a separate note:
I use the variable, PtrWrkBk, to mean the pointer to workbook X.
Is this thinking appropriate?

I assume WrkBkName is open and is not the workbook with the macro in it, otherwise use

Please Login or Register  to view this content.

The message boxes should give you the relevant names - if they don't, then it's telling you something
Instead of

Please Login or Register  to view this content.

Try

Please Login or Register  to view this content.

then instead of

Please Login or Register  to view this content.

use:

Please Login or Register  to view this content.

Kev_

Thank you for responding.

In the heading of your reply, you stated: "I assume WrkBkName is open and is not the workbook with the macro in it"

In fact, the WrkBkName is open and DOES contain the macro. does this make any difference?

My thinking....and please correct me if you feel I should consider another point of view....is that the excel workbook
contains worksheets that contain critical input data for the code I wrote in the "macro" part of the workbook. Hence I kept the two together, so to speak. So when I open the workbook, the workbook IS already open. No other workbook is open, but just to be sure, I explicitly defined the workbook by name.


Otherwise, I would write the code in a blank workbook only containing vba code that i can re-use for other projects.

I did replace the variables for workbook and sheet names and used direct quotes. no change

BUT I did try using the index as , in this specific case, the FieldData worksheet has the index of 1. hence Line 5 Set PtrWrkSht = WorkBooks(WrkBkName).Sheets(1) ran WITHOUT RUN TIME ERROR
I also used your MsgBox PtrWrkSht.name approach and the proper name, FieldData did print out in the MsgBox.


I would like to understand why my worksheet name, "FieldData" does not work......but at least I know the code on line
5 is okay with the onlyl problem being the sheet name.

any more thoughts>

Does you sheet name have a trailing space perhaps?
Run this and count the letters:

Please Login or Register  to view this content.

please mark the thread as SOLVED - thread tools, top of thread