Run Type Error 424: Macro to load data from a different workbook

[SGraber]

Greetings,
I am new to this forum. I have a macro I've been working on in which I am trying to load a data range from another (unopened) workbook. As my title alludes to, I keep getting error code 424, Object Required. Not sure how to resolve this. Any help would be greatly appreciated.

The open file and tab I am working on is FH4A Appraisal and the workbook I am trying to load data from is entered as a url path in FH4A Appraisal and is opened with the copyaddress function.
My code is below and the line the error is happening on has ** after it.

Please Login or Register  to view this content.

[Norie]

What exactly is copyaddress when you get the error?

Do you actually have a function caled copyaddress?


PS Can you add code tags?

[SGraber]

Hi Norie,
I'm actually not completely sure what the copyaddress function does. I pulled that code from another macro. It worked for me on a different macro, but isn't on this one for some reason. I believe what it does is look at the url path I designated within the spreadsheet and then copies and opens that file specified there; but I could be wrong about that.

How do I add code tags?

[SGraber]

I think I just figured out how to add those code tags

[Norie]

Is copyaddress definitely a function?

I'm asking because you don't assign a value to a function as you appear to be doing here.

Please Login or Register  to view this content.

That looks more like copyaddress is being treated as a variable, perhaps for the filename/URL of the workbook to open.

If you click the hyperlink in my signature or in my last post you should find out how to use code tags.

[SGraber]

I don't know that copyaddress is definately a function. Just guessing, and probably guessed wrong
Since I can manually update the filename/url path would that show copyaddress as a variable? Changing that url is part of the usefulness of this macro. That way we can access multiple reports. So if it is a variable, do I need to define it in a different way?

[Norie]

Can you upload an example workbook with the code?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[SGraber]

Just uploaded the 2 relavant files. I changed the sensitive data, but it shouldn't affect the code at all.

[Norie]

There's no attachments in your post.

Where did you upload them?

[SGraber]

I attached the files to my original post.

[Norie]

Oops, I've been a bit thick.

The problem is that it should be Workbooks.Open not Workbook.Open.

If you change that you'll also find another problem, merged cells.

The named range 'fh4aAppraisalPath' covers the merged cells D8:I8, so Range("fh4aAppraisalPath") will return an array of values from those cells.

To only get the value you can use this.

Please Login or Register  to view this content.

So the start of the code would look like this.

Please Login or Register  to view this content.

Or if you wanted to tidy things up a bit.

Please Login or Register  to view this content.

[SGraber]

Very Nice! We also just discovered that those merged cells were causing an issue. I'll have to check out your clean version of the code, but those two issues solved it. Seperate question, do you happen to know why it's important to define variables up front? I see a lot of people encouraging that by using of Dim but I've never (except for this macro) used Dim in my code before and also never had issues. Does not defining variables cause issues in larger/more complex macros?

[Norie]

You should always declare your variables, especially if you are working with large/complex code.

I've seen many examples, one recently actually, where someone has either not declared or incorrectly declared variables in large routines and found out the code didn't work as expected.

A lot of times all that needs fixed is a few variable declarations to get everything up and running.

Here's a little more about variable declaration.

[SGraber]

Thanks again, I'll check out that link.

[SGraber]

How do I show this as solved? Is there an option somewhere?