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:

Sub project()


    Dim filter As String
    Dim caption As String
    Dim RB_Filename As String
    
    Dim RB_workbook As Workbook
    Dim RB_sheet As Worksheet
    Dim RB_Lrow As Long
    Dim RB_Lcol As Long
    Dim RB_rngFilter As Range
    
    Dim Master_workbook As Workbook
    Dim Master_sheet As Worksheet
    
    Dim Aging_workbook As Workbook
    Dim Aging_worksheet As Worksheet
    Dim Aging_Filename As String
    
    Dim Ws1_Lrow As Long
    Dim Ws1_Lcol As Long
    
    Dim rngCopy_Red As Range
    Dim rngCopy_Black As Range
    
    
    
    ' make weak assumption that active workbook is the target
    'Set Master_workbook = Application.ActiveWorkbook '.Open("Master Template.xlsm")
    Set Master_workbook = ThisWorkbook
    
    
    ' get the R&B  workbook
    filter = "Text files (*.xlsx),*.xlsx"
    caption = "Please Select an input file "
    MsgBox "Please insert R&B inventories file "
    RB_Filename = Application.GetOpenFilename(filter, , caption)
    
    ' get the aging workbook
    MsgBox "Please insert Aging  file "
    Aging_Filename = Application.GetOpenFilename(filter, , caption)
    
    
    'If Cancel then exit
    If TypeName(RB_Filename) = "Boolean" Then Exit Sub
    
    
    Set RB_workbook = Workbooks.Open(RB_Filename, ReadOnly:=True)
    Set Aging_workbook = Workbooks.Open(Aging_Filename, ReadOnly:=True)
    
    ' copy data from R&B workbook and Aging  to Master_workbook
    
    Set RB_sheet = RB_workbook.Worksheets(" qry Active")
    Set Aging_sheet = Aging_workbook.Worksheets("base_data")
    RB_sheet.Activate
    RB_sheet.Select
    Aging_sheet.Activate
    Aging_sheet.Select
    
    With Master_workbook
            'first remove the 'qry Active' worksheet from Master (if it exists)
            On Error Resume Next
            Application.DisplayAlerts = False
            .Worksheets("qry Active").Delete
            .Worksheets("base_data").Delete
            Application.DisplayAlerts = True
            On Error GoTo 0
    
            'copy the qry Active ws to Master
            RB_sheet.Copy After:=.Sheets(.Sheets.Count)
            Aging_sheet.Copy After:=.Sheets(.Sheets.Count)
    End With
    
        RB_workbook.Close savechanges:=False
        Aging_workbook.Close savechanges:=False
        
        ' Filter data for red and black stock
    
    Master_workbook.Worksheets("qry Active").Activate
    
    'Ws1_Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    'Ws1_Lcol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    With Master_workbook.Worksheets("qry Active")
    
            Ws1_Lrow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
            Ws1_Lcol = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
            Set rngFilter = .Range(.Cells(1, 1), .Cells(Ws1_Lrow, Ws1_Lcol))
            
            Dim Lastrow_Base As Integer
    
    
            Sheets("qry Active").Columns("B:B").AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=.Range("Y1"), Unique:=True
            Lastrow_Base = .Cells(.Rows.Count, "AY").End(xlUp).Row 'Unique:=True
          
             Dim i As Integer
             Dim x As Range
       
             For i = 2 To Lastrow_Base
            
                    Set x = Master_workbook.Sheets("qry Active").Range("Y" & i)
                        If x <> "" Then
              
                            ' create New workbook and add sheets
              
                             Set NewBook = Workbooks.Add
                        With NewBook
                             .Title = x
                             'add your additional code here
                             NewBook.Activate
                             NewBook.Worksheets.Select
                             NewBook.Worksheets("sheet1").Name = "Red "
                             NewBook.Worksheets("sheet2").Activate
                             NewBook.Worksheets("sheet2").Name = "Black"
    
                            ' Filter data for red stock
                        With rngFilter
                     
                            .AutoFilter Field:=5, Criteria1:="Red ", Operator:=xlFilterValues
                            .AutoFilter Field:=4, Criteria1:="Standard", Operator:=xlFilterValues
                            
                            .AutoFilter Field:=25, Criteria1:=x.Value, Operator:=xlFilterValues
                        
                            Set rngCopy_Red = .SpecialCells(xlCellTypeVisible)
                                                .AutoFilter ' Switch off AutoFilter
                        End With
              
                             rngCopy_Red.Copy NewBook.Worksheets("Red").Cells(1, 1)
             
                            ' Filter data for Black stock
                        With rngFilter
                     
                            .AutoFilter Field:=5, Criteria1:="Black ", Operator:=xlFilterValues
                            .AutoFilter Field:=4, Criteria1:="Standard", Operator:=xlFilterValues
                            
                            .AutoFilter Field:=25, Criteria1:=x.Value, Operator:=xlFilterValues
                        
                            Set rngCopy_Red = .SpecialCells(xlCellTypeVisible)
                                              .AutoFilter ' Switch off AutoFilter
                         End With
              
                            rngCopy_Red.Copy NewBook.Worksheets("Black").Cells(1, 1)
                                
                               .SaveAs Filename:="KPI" & " " & x & ".xlsx"
                                NewBook.Close
    
                         End With
                         Else
                         If x = "" Then
                            Set NewBook = Workbooks.Add
                        With NewBook
                             .Title = x
                             ' add your additional code here
                             NewBook.Activate
                             NewBook.Worksheets.Select
                             NewBook.Worksheets("sheet1").Name = "Red "
                             NewBook.Worksheets("sheet2").Activate
                             NewBook.Worksheets("sheet2").Name = "Black "
             
                        With rngFilter
                     
                            .AutoFilter Field:=5, Criteria1:="Red ", Operator:=xlFilterValues
                            .AutoFilter Field:=4, Criteria1:="Standard", Operator:=xlFilterValues
                            
                            .AutoFilter Field:=25, Criteria1:=x.Value, Operator:=xlFilterValues
                    
                             Set rngCopy_Red = .SpecialCells(xlCellTypeVisible)
                                                .AutoFilter ' Switch off AutoFilter
                                                           
                        End With
             
                                rngCopy_Red.Copy Destination:=NewBook.Worksheets("Red ").Cells(1, 1)
              
                        With rngFilter
    
                            .AutoFilter Field:=5, Criteria1:="Black ", Operator:=xlFilterValues
                            .AutoFilter Field:=4, Criteria1:="Standard Component", Operator:=xlFilterValues
                            
                            .AutoFilter Field:=25, Criteria1:=x.Value, Operator:=xlFilterValues
                    
                                Set rngCopy_Black = .SpecialCells(xlCellTypeVisible)
                                                    .AutoFilter ' Switch off AutoFilter
                        End With
              
                                rngCopy_Black.Copy Destination:=NewBook.Worksheets("Black").Cells(1, 1)
             
                                .SaveAs Filename:="KPI" & " " & "No SCM" & ".xlsx"
                                 NewBook.Close
          
                        End With
        
                        End If
                        End If
            Next
    
    End With
    
    
    End Sub

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.