setting up a user interface to excel using VBA

[sreekanth.buddha]

I am a beginner to play with excel especially doing user interface in of it.

I have a requirement in my company i.e every month they use to get data from the global companies and we have to report it through excel. What I mean is : we had a data that has to be uploaded or make a file path in excel in order to fulfill my requirements.

Requirements:

A(F.name) B(l.name) c(email_id) D(stock) E(revenue $) F(12mt revenue)

1.Ram loki rl@gmx.com Black stock 12345 3455
2.joseph fist jf@gmx.com Black stock 45555 5555555
3.raj mani rm@gmx.com Black stock 66677 33255
4.puv uday pu@gmx.com Red stock 9876 7890
5.sri harish sh@gmx.com Red stock 4433 355
6.mattiah schmidt schmidt@gmx.com Black stock 0 678899999


1. Based on certain `column('D')` the data has to be separated from the raw data into another `sheet('black stock sheet')` of same workbook but the `column 'D'` should not be displayed in `sheet('black stock sheet')`
2. Based on the `column 'E'` should be sorted in descending order(only column 'e') and the coresponding rows should also be changed but not in descending order.

Actual result:

A(F.name) B(l.name) c(email_id) d(revenue in $) e(12m revenue)

1.raj mani rm@gmx.com 66677 333255
2.joseph fist jf@gmx.com 45555 5555555
3.ram loki rl@gmx.com 12345 3455
4.mattiah schmidt schmidt@gmx.com 0 678899999

At last I have to see the sheet in this manner and also I need this as user friendly interface so that the fucntional guys can use it even if I'm not in the company.

Here is my code so far...

Sub Black_stock()
    
    
      Dim colWs1Last As Long
      Dim rngFilter As Range
      Dim rngCopy As Range
      Dim rowWs1Last As Long
    
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim ws3 As Worksheet
      Dim ws4 As Worksheet
      
      
      Set ws1 = Worksheets("Inventory Activation ")
      
    
      On Error Resume Next
      Set ws2 = Worksheets("Black Stock")
      On Error GoTo 0
    
      If ws2 Is Nothing Then
      If ws3 Is Nothing Then
       
        Set ws2 = Worksheets.Add(After:=ws1)
        Set ws3 = Worksheets.Add(After:=ws2)
        Set ws4 = Worksheets.Add(After:=ws3)
        ws2.Name = "Black Stock"
        ws3.Name = "Red Stock"
        ws4.Name = "Aging Stock"
      Else
         With ws2
          .Cells.EntireRow.Delete
          .Activate
        End With
         With ws3
          .Cells.EntireRow.Delete
          .Activate
        End With
        
         End If
         End If
         
    
    With ws1
    
       
        rowWs1Last = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
        colWs1Last = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
        Set rngFilter = .Range(.Cells(1, 1), .Cells(rowWs1Last, colWs1Last))
        
            
        If .AutoFilterMode Then
          .AutoFilter.Range.AutoFilter
        End If
        
      End With
      
        With rngFilter
        
          rngFilter.AutoFilter Field:=4, Criteria1:="black stock", Operator:=xlFilterValues
          
          Set rngCopy = .SpecialCells(xlCellTypeVisible)
          .AutoFilter
                
        End With
        
        
        Columns("D").EntireColumn.Delete
        rngCopy.Copy ws2.Cells(1, 1)
       
              
       For Each cell In rngFilter.CurrentRegion.SpecialCells(xlCellTypeVisible).Rows
        
    
    Next cell
    
          
     End Sub

My code has to work as it filters all the black stock in another sheet of same workbook as well as for red stock also and these black and red stock should not appeared as column in thier respective sheets.

Im trying to add a sheets in the workbook through code but at the end i got only two sheets and not the third sheet and later i need to add more sheets also.

`Worksheets("Inventory Activation ")` is raw data and i have to extract data from this sheet('Inventory Activation').

I got results of black stock but i want to delete the unwanted columns or make it invisible and also the column('D') have been appeared in worksheet('Black stock') which i dont want to be included in the sheet of 'black stock'

can someone please have a look at my code.

Thanks in advance!

Regards
Sreekanth