How to not get run time error when clearing data with VBA

[bdouglas1011]

I have a Name Range Called "Body" which references "='Daily Chgs'!$B$3:$BN$45,'Job Ticket'!$f$10:$h$52,'Job Ticket'!$M$10:$M$52,'Job Ticket'!$E$58"
I also have a macro For clearing the data in that range.

Please Login or Register  to view this content.

When I select the macro to run i get a runtime error '1004 reference is not valid

This used to work when I cleared data only within one sheet ....I am now trying to clear a range of cells on another sheet also and now it wont run.

What would be the problem?

[stnkynts]

I could be wrong but I don't think VBA is going to work with a named range when that named range references multiple sheets. A way around this could be to directly reference the ranges in code but if you are consistantly changing the cell references of the named range "Body" it will not be efficient for you. If you keep them the same try this:

Please Login or Register  to view this content.

[bdouglas1011]

I tried that and I am getting runtime error 1004
Method 'union' of object'_global' failed

Any thoughts

[Norie]

Does this work?

Please Login or Register  to view this content.

If it doesn't does this work?

Please Login or Register  to view this content.

[stnkynts]

Woops I think i spelled Daily as Daly. Double check the sheet names in the code i provided to make sure they match yours.

[Norie]

stnkynts

You can't union ranges that are on different sheets.

[bdouglas1011]

I did I change the spelling and same error

[bdouglas1011]

Thanks for the info

[stnkynts]

Ahh thanks Norie