Runtime error 1004 application-defined object-defined

[stevoDE]

Hi

I'm getting a runtime error 1004 message on the following code when trying to execute the line in bold and I don't know why. I have checked to see if the macro is in module as opposed to a sheet and it is so that's not what is causing the problem. It works fine if I am in the sheet I am trying to execute the code on but fails if I don't have that sheet activated i.e. the first line of code also fails unless I am in that worksheet.

New to VBA but I'm guessing it's something to do with the way I have declared Thisworkbook.

Please Login or Register  to view this content.

Any help appreciated.

[Norie]

Try this.

Please Login or Register  to view this content.

[stevoDE]

Thank Norie. I'll try this in the morning & report back.

Out of interest, why do we have to use 'with' and why separate statements for each line of code? It seems quite longwinded. Is there actually any benefit in declaring a variable for thisworkbook in this instance as opppsed to just using Workbooks(.....)? Apologies for so many questions, still trying to get to grips with thisworkbook and when to use it.

[Norie]

You don't have to use With, here's an alternative way of doing things.

Please Login or Register  to view this content.

As for using variables, again you don't have to, but it might help to use variables with an appropriate names to help identify the workbooks you are working with.

If you use Workbooks(...) instead of ThisWorkbook you would need to know the exact name of the workbook the code is in which probably won't change but you never know.

[stevoDE]

The above works Norie. Thanks for the info and your help.