Compiling a Modelling Excel Output File

[kiltro]

I am using Caesar II to model pipe and when you export the results to Excel it creates one sheet per case and I typically have 80ish cases. For now I have been deleting the excess information and cutting/pasting the relevant information all into one sheet.

I would love a macro that went through all the sheets and applied the same changes to each one and then compiled them into a new sheet.

This is my first macro attempt and the closest I have gotten to a practical macro is this

Please Login or Register  to view this content.

All this does is delete the excess information to the sheet I am on and then cuts whats left. Moves me to the sheet I am compiling too, in this case "AWPJ (Short)", and then it asks me what cell I would like it pasted into.

Any attempts at finding the last filled row so I can have it automatically paste two rows below this filled row have failed.

As have my attempts to get it to go through all the sheets and apply the formatting changes all at once.

The holy grail would be for the macro to apply all the formatting changes and then compile all the sheets together.

I start with a sheet like this.

Please Login or Register  to view this content.

And compile them all so they look like this

Please Login or Register  to view this content.

Any would help would be greatly appreciated.

[StephenR]

Please attach a sample workbook illustrating raw data and desired results.

[kiltro]

Attached is a sample.

The first twelve sheets is what it looks like when Caesar II exports it to Excel. The 13th sheet is what the compiled information looks like.

Thank you for your time.

[StephenR]

Try this. Your results don't seem to relate to raw data but guessing this is just for illustration. The code adds a sheets at the end for results.

Please Login or Register  to view this content.

[kiltro]

Thank you it worked flawlessly!!

[StephenR]

Good. Strictly speaking this line should be changed:

Please Login or Register  to view this content.

[kiltro]

It's working fine now. What is that line change for.

Is it addressing the extra sheet (sheet 1) that is at the end of the Caesar II output?

Regardless I would like to tag this thread as solved, how do I do that?

[StephenR]

Yes, that's right.

See "How-to" at the top of page.