Floor Plan Macro - Need help troubleshooting 'Run time error 9' error message

[liddy]

Hello all,

Recently I made a post in the formula forum. I needed help developing a way to make a floor plan within Excel. I ended up with a working prototype due to the help of some wonderful people, and I began to work on applying the code they supplied me with on a much higher level.

Here is the code that was given to me on the forum. This code works perfectly, albeit within only one workbook.

Please Login or Register  to view this content.

Sheet 1 refers to the TEMPLATE of the floor plan.
Sheet 2 refers to the EMPLOYEE DIRECTORY that contains a basic table. This table starts at A1, and within it is the room # of the employee (column A) and the name of the employee (column D)
Sheet 4 refers to the FINAL FLOOR PLAN - This is what will be populated with data from the EMPLOYEE DIRECTORY, referencing the TEMPLATE to correctly place the names.

Although this code works great, it only works within one workbook. The desired end goal of this project is to have 3 separate workbooks. One workbook will contain all of the templates, one will contain all of the directories, and one will contain all of the populated floor plans. The workbook with the populated floor plans will be used regularly to keep track of employee/tenant locations. The other two workbooks will be updated regularly as employees/tenants change.

Therefore, I modified the code I was given to try and complete this task. I ended up with this:

Please Login or Register  to view this content.

When I run this code, I get an error message (Run Time Error 9: Subscript out of Range)
When I F8 throughout the code, the error message occurs once I pass the Set functions (Ln22)

There is a very solid chance that I am making a very simple error; I am fairly new to VBA and most of this stuff is going over my head, although I am learning fast.

Attached are the 3 workbooks if you would like to try it out for yourself. Thank you for helping me out!

-Liddy

[Logit]

.
I believe you need to provide the entire path to each workbook.

[kvsrinivasamurthy]

If problem is with "Set"
If It is workbook all the files should be in the same folder in which this macro file exists. Otherwise entire path of file should be included.
If it worksheet That Sheet may be missing.

[liddy]

.
I believe you need to provide the entire path to each workbook.

If problem is with "Set"
If It is workbook all the files should be in the same folder in which this macro file exists. Otherwise entire path of file should be included.
If it worksheet That Sheet may be missing.

Is this a step in the right direction?

Please Login or Register  to view this content.

I am confused about how excel would like these workbooks to be called upon. When I look it up online, sometimes it says that the workbook has to be active before I call upon it. Other sites say that I can use the method I am using, however, it is obviously not working.

I still get the same error when I run this code, in the same location.

[kvsrinivasamurthy]

Change it this way for all files

Please Login or Register  to view this content.