Combine multiple sheet data into one main sheet

Hi,

I came across a requirement where I need to combine multiple sheets having reports into one main sheet. the data need to be combined in such a manner that each report data field must come under one another.

Attached is the data file for reference.

SO template tab is the summary where I want the reports to be combined.
Sample reports are attached, Reports are the same for all tabs.

In reports unique field is quote number.


Kind regards,

Hi
It's not really clear which part you want to automate.
Your report output tabs are empty except Report_Output1

All those headings in SO template tab should populate automatically when reports are inserted in file. There are 30-40 reports which are combined by me as shown in SO template tab. Each report have data that needs to be pasted. Report 1 is pasted in SO template tab as example. Rest of tabs are empty now but they will have similar data to report 1 arranged in similar manner.

which headings? I don't see correlation between 'SO template' and 'Report_Output1'

Hi,

Data that is filled in SO template tab is from Report_Output 1 tab. This data should come automatically from all the reports attached stacked one after another in SO template. All these fields that are entered in SO template are highlighted in Report_Output 1 and is available there.

Thanks

Is the grey part coming from Report_Output1?
Or do you mean line 5 and down?

Yes from line 5 and down

Please put sample data in "SO template": where does data in column H onwards come from?

Originally Posted by haroonkhurshid

Yes from line 5 and down

OK! that was not obvious
Now, do you combine manually all Report_Outputs into 1 tab? (in your example, 'Report_Output1') ?

Seems more like a complete make question to me. With a macro.

Please Login or Register  to view this content.

I believe the change required is shown in RED

Please Login or Register  to view this content.

Thanks John for quick resonse

This code works if the Quote is not present all together in the report then it will give value from A3. It does not work if one quote is there and the other is missing:

For example,
In report
Cell A33 is empty
Cell A54 has quote

it will give C06 instead of C00

C06 is A33 and A54 if either are present so you appear to want C00 whether C06 (A33/A54 is present).

If A33 is blank result is C00, if present C06

If A54 is present , result is C06, if absent C00

There could be several ASD parts list in the report for which separate Quote no. is available and sometimes quote no. is missing for one of the list.
Logic should be like even if one quote no. is missing for any ASD part list take C00. if all are present take C06

Sorry, i forgot to mention this exception

So there is need to check how many ASD parts lists there are, then check if everyone has a quote, and if any missing use C00.

This requires some additional coding as the C00/C06 values are determined as the rows are processed sequentially.

As suggested by Vraag, post a better sample file.

Here is the sample having all scenerios covered

I used the code suggested by you and forgot to save the file as .xlsm
I tried so many things to get it working in the correct manner. I am a beginner to VBA. so please dont mind


My apologies.

This time conditions will not change. I will mark thread as solved after getting solution to this. It is my humble request please help. I am new and learning VBA and looping and conditions are tricky to get right.

Please Login or Register  to view this content.

Added FUNCTION to check if "QUotes" are present for all "ASD Parts List"

See "SO Template"

Thanks Vraag & John for providing the code and helping me. It works fine.

I spent 6 hours on the code provided earlier to work but it didn't work as expected.

I am learning the VBA. It's like magic in the world of Excel.