setting loop code to check all named sheets

[peter renton]

Hi

Code anybody help me to loop through the sheets.

at the moment the code will look at ppe sheet and check the dates if any due date are found it generates an email and inserts a table in it.
i am trying to have it check the other sheets in the work book forltruck, gas etc so that it would generate other emails with that sheet table in it
hopfully changing the subject line i.e gas insections due, ppe instection due etc.


Thank you for any assistance in advance

Peter

[alansidman]

Instead of selecting PPE, why not do a loop.


Dim w as Worksheet
For each w in Worksheets

.......code.......

next w

[peter renton]

hi

do i just need to add this at the begining of my existing code removing the select ppe row and the next w at the end or
do i need new code as well
i am just trying to get my head around this coding lark :-)

Peter

[alansidman]

do i just need to add this at the begining of my existing code removing the select ppe row and the next w at the end

Yes.

Here is a link on this concept.

Macro to Loop Through All Worksheets in a Workbook (http://support.microsoft.com/kb/142126)

[peter renton]

Hi Alan

I have tried every which way to try and get this thing to do what i want but keep screwing it up (i must be thick as it is probably very simple)
the attached selects the ppe sheet and renders the required data in the email table, no matter what i try it only shows the ppe sheet but if i remove the
ppe line it errors, how/where do i add the coding so that it will go through every sheet and render the different data from each sheet into individual email tables.

any help would be appreciated as this is now doing my head in

Peter

[alansidman]

Try the attached. Untested as I don't use Outlook as email client.

[peter renton]

Hi Alan

Thanks for looking

comes up with a runtime error on start up....method rows of object global failed..
with this row highlighted (Set rng = w.Range("F4:F50" & w.Range("F" & Rows.Count).End(xlUp).Row - 1)


Cheers Peter

[bakerman]

Tested Alan's code and made some minor changes.:thumbsup:
This does the trick for me.
Try it out and feel free to ask if you have any further problems.

[peter renton]

Thanks Bakerman

that works spot on, i would never have got there in a month of sundays

thanks also alan for your assistance..

Peter:-)

[alansidman]

You are welcome. Thanks for the Rep.