generate report - consolidation macro

[rickmeister]

Dear,

I would like to receive some help with following macro...
I have a master report file : UAT_TEST_CASES_MASTER for which I want to generate a report based upon other input sheets (for example UAT_TEST_CASES_XXX, UAT_TEST_CASES_YYY, ...)

the macro should:
- be included into the master test cases
- insert pop-up message which file to use as input to copy to master file
- copy all lines looping all worksheets of the input file where the column test executor (column I) is completed with a value and pasting it to the same worktab (same name in master).
- when running the macro for multiple users, the macro should not overwrite the lines but continue where the last line was injected.

Objective: to make a consolidation of different input files into one master file.

I hope this is more or less clear
I attach in attachment both example files.

Thanks in advance for your promt assistance!

Erik

[rickmeister]

I have made a first attempt, however the macro runs but without any result

I would like to copy all not nulmll values that can be column I (looping all the sheets per input file) to the master sheet in the appropriate worktab

Please Login or Register  to view this content.

[rickmeister]

Please Login or Register  to view this content.

the found rows should be copied to the appropriate sheet - currenlty nothing is copied.. How can I solve this?

[apo]

Hi..

This should do it..

I didn't quite understand what the "ISU" value was meant to be.. so I assumed it was a Name abbreviation (of the Test Executor)...

To test .. I have added a few rows of "AFA" as the executor in your test case workbook for each sheet.. so i autofiltered on Column 9 (not 10).. but this will be easy to change if my assumption is wrong..

Click the button and type "AFA" .. no quotation marks.. into the input box..

It will then loop through each sheet in your test case workbook.. autofilter for any rows that were tested by "AFA" and copy them to the relevant Sheet in your Master Workbook.

I have added both files in a Zip file below..

Please Login or Register  to view this content.

[rickmeister]

Hi Apo! Thanks for your feedback! I have run your macro on the master sheet in attachment using as input the file UAT TEST CASES EHE but I get the following error:

Workbooks(wbname).Sheets(wbstr).Range("A15:M" & lr - 2).AutoFilter 9, ISU
Run-time error 1004: application defined or object defined error

Also when running your macro on the sheets you have provided I get the same error..

[apo]

So did you try it with the test case file that was attached.. did that work for you as it did I?

Also.. was i correct in thinking that the Test Executors Names (3 character abbreviation) is what is meant to be entered?

[rickmeister]

Aha it worked, I 'll try to finetune it and see what it brings, keep you posted!

[apo]

Hi..

I think the issue is that.. with Test Case file you posted.. the first 2 sheets were bypassed as was the last.. I coded for that..

BUT..

The Test case you just sent me.. the first 4 sheets needed to be bypassed..

So this..

Please Login or Register  to view this content.

needed to be changed to this:

Please Login or Register  to view this content.

Probably a good idea if you have all your test case sheets have the same sheet structure..

I just tested the Test case with that change and it worked fine.. lemme knwo how you go..

[rickmeister]

Indeed, I found it also out!
working well now! super!! thanks a lot!!!!

[apo]

Hi.. thats great..

btw.. I replaced the zip file in Post #4.. there were 4 extra non related files in it.. no idea how they got there.. I blame the beer..