Run-time error '9' with ThisWorkbook.Sheets Function

[shibbyz]

I have run into the problem with ThisWorkbook.Worksheet function. I started doing tutorials by VBAIsFun on youtube and I am running into trouble with this function.

Here is the tutorial that I am having trouble with : Excel VBA Basics #7 - Using specific sheets, Hiding, Unhiding and Selecting with VBA - YouTube

So I got the Run-time error code '9' when I went to run it. I changed all of the Thisworkbook.sheets command to Activeworkbook.sheets command and it worked when I ran it from VBA. I then went and tried to run the code from the excel sheet (Alt+F8) and no macros came up as available to run.

This leads me to believe that there might be some sort of link between the VBA code and the excel sheet that is missing. Any thoughts or suggestions are greatly appreciated.

Thanks

 

[VBA Geek]

"Thisworkbook" always identifies the workbook in which the running macro is stored.
Activeworkbook can identify ANY workbook that is active at a particular point.

 

[shibbyz]

How do I ensure that that macro is stored within the workbook? I opened Visual basic up from the excel sheet that I am working. Is there a specific set of steps that I need to take to associate the code with that specific workbook?

 

[VBA Geek]

if a given code is stored in workbook "A" then anytime you see in the code "thisworkbook", it is referring to workbook "A"

I like to SET all the worksheets that my code will use, for instance if i know that during a code i will access several times worksheet "Report" in workbook "B" I will
dim Rep as Worksheet
set Rep = workbooks("B.xls").Worksheets("Report")


you can also set workbooks the same way

this way whenever i type Rep I am easily accessing the exact sheet in the exact workbook that I want. It's good practice and makes your code easier to read

 

[shibbyz]

Thanks a lot for the input. I had my sheets all messed up. I had to start over and now it is working. I'm not sure if it has something to do with the file format. I was using a file that had been converted from office 2007 to 2010. It seems to be working well for now.