Cleaning multiple sheets of data for creating one sheet to be used as pivot source

Hello Experts,

Every month I get a workbook with multiple sheets in raw format which requires a lot of time clean and get it in one sheet to be used as pivot table data source.
With the help of some experts I have tried to build a code which will

1. Unmerge all the merged cells in all the work sheets (Coz some cells in all the sheets are merged)
2. Find all the worksheets name containing SMRY or SUMMARY and delete
3. In column A of all the remaining worksheets find string AAA & delete all the rows including AAA and above
4. In column A of all the remaining worksheets find string BBB & delete all the rows including BBB and below

But the code below is giving me runtime error.

Besides above I would also like to add a column where it adds the sheet name in each row.

Please advise/help.

Best Regards/VKS

Please Login or Register  to view this content.

first:

remove

Please Login or Register  to view this content.

from the beginning of sub

then rewritted code for cleaning above AAA and below BBB could look like:

Please Login or Register  to view this content.

PS. If the code stops with error always let us know the error message and in which line it stops. Posting a (sample - no delicate data and not too big one) workbook is always a good idea.

obviously a typo. myramge makes no sense :-) change to myrange

Please Login or Register  to view this content.

as there was no sample file attached (see forum rules) - all corrections were just written in forum editor and not tested :-)

let me autoquote myself:
"Posting a (sample - no delicate data and not too big one) workbook is always a good idea."
Otherwise I have no chance to check what happens (May be you have AAA or BBB more than once in a sheet or BBB above AAA or ... or ??? anything)

Of course you can debug it yourself. In editor do step-in (F8) and observe values in Locals window (or setup some watches) and changes your workbook.