Problem with Syntax when I have more than one Workbook object defined

[nquadr]

I have the following global variables:

Please Login or Register  to view this content.

I have successfully opened the files, I can move between workbooks select particular cells so I know that my instantiation is correct, however in this procedure I am getting an error of

Run-time error '1004'
Application-defined or object-defined error.

When I run this code:

Please Login or Register  to view this content.

The line that is giving me the error is

wb1.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Select

I am sure it has something to do with Syntax of the code, not sure how to correct it. Any help will be greatly appreciated. Thanks.

What I am trying to accomplish is count the number of rows and then if successful count the last columns so I can select it as a range and then filter.

[StevenM]

Please Login or Register  to view this content.

(You should write you code without select and activating things, but that is not what you asked.)

If you really have to do it this way, then you probably need something like:

Please Login or Register  to view this content.

Then the active workbook will have an Active Sheet, so:

Please Login or Register  to view this content.

[nquadr]

I have already tried that, unfortunately that does not work either. Any other ideas?

[nquadr]

I have already tried that, unfortunately that does not work either. Any other ideas?

I have already tried that, unfortunately that does not work either. Any other ideas?

More specifically, it does not work in the wb1 workbook. It actually executes in the excel file that the macro resides in. I want it to execute in wb1. I do not have that problem wb1.ActiveSheet.Cells(1,2).Value = "Mnemonic" as above.

That is why I am assuming that it is a problem with the Syntax.

[StevenM]

Can you upload the workbook?

[nquadr]

I have changed some of the data and I am uploading 2 of the workbooks rather than 6 of them. Hopefully you can work with this.

[nquadr]

I have changed some of the data and I am uploading 2 of the workbooks rather than 6 of them. Hopefully you can work with this.

I am assuming that is what you need?

[StevenM]

There was no code in your workbooks, nor is there any description of what is needed to get done.

[nquadr]

I have modified the code to make it easier for review, just modify the pathnames.

[StevenM]

Okay, I was wrong. It seems you can use:

Please Login or Register  to view this content.

But errors would occasionally pop up if I looked at a worksheet while the code was executing, because this would at times change which sheet is active. Otherwise, the line which you said didn't work, actually worked for me more than once.

I bet your macros would work better if you didn't use the Active Sheet and Select. It would also be easier to watch what is going on.

For example, to find the last row in column A of wb1 you should try:

Please Login or Register  to view this content.

Note: A Range statement or Cells statement without a dot before it refers to the Active Sheet,
but a range or cells with a dot before it (and nothing else) refers to the With statement.
Thus in the above code, both (!!) .Cells & .Rows refers to wb1.Worksheets(1).

Also:

Please Login or Register  to view this content.

Could be just:

Please Login or Register  to view this content.

And why this?

Please Login or Register  to view this content.

I couldn't get the above to work (of course, I added the file names), so I had to change it to the more traditional.

Please Login or Register  to view this content.

[nquadr]

Thank you so much!

Finally got it to work!