Method 'Range' of object '_Global' failed when trying to apply a dynamic range to sheets

Hi all, very new to VBA and would appreciate any help. I've been stuck on a problem that I can't seem to get past no matter what I try.

I'm working with a file that has dozens of tabs. I have a calculation that dynamically sets a print range for each sheet in my workbook depending on the contents of that sheet. I am trying to develop a macro that will then delete all of the rows below where the print range ends in each sheet which will be a different row on each one.

Currently I have the Macro working only on my active sheet - and whenever I try to apply it to each sheet I get the error Method 'Range' of object '_Global' failed. I'm assuming that this is because of the reference to ActiveSheet in the VBA but whenever I try to replace it I am getting the 'Object Required' error.

I am completely stumped - would appreciate any help with this problem.

Attaching a snippet below - here it only deletes the rows on the Active Sheet and when I try to apply a for each worksheet in this workbook component I get the Method 'Range' of object '_Global' failed error - how can I successfully delete the rows on each sheet?

works on active sheet.PNG

This attempt results in an 'object required' error on the highlighted area

Attachment 620739

Hi there,

Welcome to the Forum.

In general, posting your code as a picture is not very helpful as we can't copy it to a VBA CodeModule - it's much better to post your code as text surrounded by Code Tags.

It's late at night here so, rather than re-typing all of your code, I'm just posting what I think you need to include in order to achieve what you want:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks for the help Greg - and noted about the text rather than pictures.

When I run as per the below I get the error Method 'Range' of object '_Global' failed again on the 'Set PrintRange = ' line

Here is how I altered what I had previously:

Please Login or Register  to view this content.

Hi again,

Many thanks for your feedback.

Does the following code help to move things along:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

This has solved it! Can't thank you enough!

Hi again,

Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated!

You're very welcome - glad I was able to help.

Best regards,

Greg M