Generating report vba

[KK1234]

Hi all,
I am using this code and it works fine.
I want to modify it as follows and something is not right. Need your help please.
Sample attached
Many thanks
Kind regards

Please Login or Register  to view this content.

[AB33]

Do you mean?

Please Login or Register  to view this content.

[KK1234]

Thanks AB33 .
That works. Great.
But any suggestions on reading data from sheets 1 to 8 only .
Currently due to the line below it reads all and if I include another sheet it stops working.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[TMS]

Try:

Please Login or Register  to view this content.

This just checks Sheets 1 to 5 as that's all that exist in this workbook


Regards, TMS

[AB33]

KK1234,
You could use sheets count from 1 to 8, but this is not good idea. You could use either array, i.e. you can put in all sheet names in array, or better is if the sheet names have some similarity or pattern where you can include or exclude one or more sheets

[TMS]

You could use either array

I did that ...

[AB33]

Hi Trevor,
I was typing and did not see your reply

[TMS]

s'OK, no problem, just sayin',

[KK1234]

Hi,
I think I am blessed by both Gurus.
Lucky me.
AB33 and TMS it has been a great help form you. It works perfect
Great thanks from me.
Just one more question.
In original sample book posted I am also planning to insert page breaks after last row copied from any sheet.
Do you think it is possible please.
I will close this thread after I get reply.
Thanks again
Kind regards

[KK1234]

Something like

Please Login or Register  to view this content.

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[KK1234]

What an EXPERT solution TMS.
Great work.
I am studying your code now to understand it and learn.
That's the main line I was struggling with.

Please Login or Register  to view this content.

Thanks again

[TMS]

Well, the full stop/dot (.) links the HPageBreaks to the ws referred to previously in the line "With ws", so it's applying the Horizontal Page Break to the ws worksheet.

You have to tell it where to add the Page Break and you do that by referring to a range and the break is inserted BEFORE that range. lNR is just my shorthand for a long variable which holds the Next Row number. And lNR is determined in the line of code before and is based on the number of cells in column A.

And, confusingly, it is a Horizontal Page Break because it goes across the page, hence, horizontally, although it is effectively splitting the page vertically.

Hope that clarifies that line of code and how and why it is used.

Regards, TMS

[KK1234]

Thanks TMS.
It is a great learning for me.
Very well explained.
Thanks again.
Kind regards

[TMS]

You're welcome.