How to consolidate multiple workbooks into summary workbook using VBA

Dear Forum Members,

Due to very urgent situation, i am reposting this thread and not aware about policy to repost and apologies for that.

1. I need your guidance and technical inputs in the following requirements:
2. I have a master data set (workbook) to collect the details of visitors to the counselling center regularly, which has been shared with various 30 counselling centers in the state.
3. I want to consolidate these 30 counselling centers master data set (Specific Sheet from the workbooks and sheet name is "Master Sheet") on every month.
4. The consolidation should be done in the "Summary Sheet" (same master data set template and already has a header).
5. Master data set range is A5 : CQ5004 (Data starts from A5) and I want to consolidate only data filed rows and should ignored blank rows in each workbook.
6. I have used the following VBA code and it works well, but it consolidates all the blank rows also. But i need only data filed row

s.

Please Login or Register  to view this content.

Appreciate your help and support on this.

Regards

Bala

Dear Mr. PJ,

Thanks for your timely support and help. When i was testing, its work well. As your narrated, it is filtered and delete the non data rows. But finally the merged entire set of data was also deleted.

Hope the problem is in this line of code. Kindly help me on this.

Please Login or Register  to view this content.

Regards

Here is the code

Please Login or Register  to view this content.

Dear Mr. PJ,

It works well and thanks a lot. But every time i use the macro, very 1st line is also deleting, due to autofilter = 1 (Code), other than that, its awesome