Cutting and pasting numbers only to adjacent column/Subtotalling

[pauldaddyadams]

Hi ,

I am creating a model which will allow me to paste reports in and the model will churn out the desired results. My problem is that the reports are in PDF and when converting to excel it throws up all sorts of formatting problems.

The guys on this forum have been a massive help in a few other posts (for which I am very grateful for) and I have a new requirement I need some help with.

On the attached I have highlighted formatting errors which I need a macro to correct for me to give me a clean report in excel. There are rules I think I could use to correct it but being VBA I do not know how to achieve this (or even follow some of the codes ive read online).

What I require is:
• All cells to be unmerged
• Then if a number is found in column A (from row 10 downwards) strip out the number element and then move this to column B (I do not need a cell to be inserted as it should already be there due to unmerging the cells).
• Then, for each “Sub Total” in Column A found I need it to clear the row B:D and then sum the rows above it until it reaches the previous subtotal.
• Finally, if the cells contains “Net Settlement” then I would need it to sum all the rows which contain SubTotal (above).

I realise this is a lot so even if some can be provided it gives me a chance to start and then I can go off googling for solutions - I expect ill be doing this all night/weekend! 

[davesexcel]

This will get you going....

Please Login or Register  to view this content.

See Attached
Not 100% complete but a good start.

[apo]

Hi..

Try this..


Press the Button on the "Original" sheet..

Result shown in Sheet1 (in the yellow section).. I have left a copy of your Required Results next to it so you can compare..

There are a couple of values in Column D that don't make sense?

Please Login or Register  to view this content.

[pauldaddyadams]

Hi

Thanks for supplying these codes! I have a couple of questions:

@davesexcel - is there any way in which the code could be amended so that if in column A it contains "Estimate Week" then it doesnt apply the rule? e.g. currently in column A I have "estimate week 1" so rightly so based on my orignal assumption it would strip out this number element.

Can that be changed at all?


@Apo - your code creates a new sheet but ideally I need it to amend the orignal sheet. The macro does strip out the number element but I would ideally like it to remove it also once found.

I would also like to tidy the report up slightly, how would this be added?

Please Login or Register  to view this content.

Paul

[davesexcel]

try this,

Please Login or Register  to view this content.

[pauldaddyadams]

Thanks Dave - that worked. I was also able to include my formatting so the report is almost done, what a relief.

I can use it currently but I will post seperatly to see if there is a code for summing rows automatically.

[apo]

@Apo - your code creates a new sheet but ideally I need it to amend the orignal sheet. The macro does strip out the number element but I would ideally like it to remove it also once found.

Try this..

Please Login or Register  to view this content.

[pauldaddyadams]

Hi Apo

That code you supplied worked also!

This is two solutions for my problem - always blown away with the helpfullness of people on this forum