need to delete unwanted similar columns across multiple workbooks in vba excel

I have multiple workbooks that I need to filter data from each and every workbook and the result must be populated into the another workbook.

Raw data:



A(PBG) B(SCM) c(Region) D(Type) E(Class) F(material) G(revenue) H(Country)
1.1000242 Rainer APE Standard Red Aluminium 1234 DE
2.1000342 Amanda APE Non-Standard Black Gold 2345 AUS
3.1000442 Rainer APE Non-Standard Red Silver 3456 IN
4.1000542 Amanda APE Non-Standard Black Silver 5678 DE
5.1000642 Sandra APE Standard Red Silver 6789 ITA
6.1000742 Sandra APE Standard Black Silver 4567 ENG
7.1000842 APE Standard Red Silver 7890 HUG
8.1000942 Micheal APE Standard Black Silver 8901 FRA
9.1000142 APE Standard Red Silver 9012 SA
10.1000042 Micheal APE Standard Black Silver 0123 NZ

According to the above example data,what I have done so far is, I have copied the range of data from the source workbooks and pasted it on the active workbook. In the active workbook I sorted the data

a. filtered data which is 'standard' in column(D)
b. filterd unique values in column(B) and those unique values i have pasted some where in active workbook of column(say Y), depends on the uniques value it should create a workbook of unique value and also worksheets of 'Red' and 'Black'. for instance , `KPI Rainer.xlsx` and in that two worksheets 'Red' and 'Black' the related data should be populated into their respective sheets. Its should be applied for blank unique values also.
b. filtered data which is 'Red' and 'Black' in column(E)
c. filtered data in descending order in column(G)

the result is populated into several excel workbooks named as

`KPI Micheal 1603.xlsx","KPI Rainer 1603.xlsx","KPI Amanda 1603.xlsx","KPI Sandra 1603.xlsx", "KPI 1603.xlsx.

what i expected is:
1. After filtering the data I have to delete the columns(C,,H) which i dont need and also delete the columns which have been sorted through column(B,D,E).
2.Please check my code and try to get rid of repeated statements which i have used in the code. To be short it should be precise.

My result:
According to this example data. I just shorten the data actually the source data has plenty of columns.
It will create 5 seperate workbooks on the name of unique value(`KPI Micheal 1603.xlsx","KPI Rainer 1603.xlsx","KPI Amanda 1603.xlsx","KPI Sandra 1603.xlsx", "KPI 1603.xlsx`) and their respective data into `red` and `black` worksheets.

My code:

Please Login or Register  to view this content.

Note: I have written code only for one source book and there are many other source workbooks which needs to be sorted out.

Please help me out.

Hi, welcome to the forum

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(I will add them for you - this time)