macro code taking too much times to create various workbooks

One reason is because you are copying the entire workbook each time you execute this line of code.

Please Login or Register  to view this content.

That line will not exclude the hidden rows. To exclude the hidden rows use this syntax.

Please Login or Register  to view this content.

Another reason for the time consumption is the formatting. As long as you do the formatting on the fly, it will add a couple of seconds per execution because of the size of the file.

Seems like you could also simplify the logic used. You use an advanced filter to get unique values from the column set to a range, then later filter the same column range by each unique value to move sets of data.

Dealing with range objects in large data sets is slow, you want to use arrays whenever possible. Copy/Paste is extremely slow with large data sets, instead dump a range from source into an array then dump array into destination range.

I also recall off the top of my head Excel 2016 having issues with not clearing filters prior to removing them.

I dont have time today to work out some code on this, but if you could post a description of what you want to accomplish (not what the current code does) I can try putting something together that is more efficient at scale.

Originally Posted by geniusufo007

Thanks Mr JLGWhiz for replying, after replacing this line of code
"rngFilter.SpecialCells(xlCellTypeVisible).EntireRow.Copy"

with this (Reversed)

"rngFilter.EntireRow.Copy"

the time has reduced to 5 minutes, Thank you very much for your help

You are welcome,
regards, JLG