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:=18, Criteria1:="black stock", Operator:=xlFilterValues

Set rngCopy = .SpecialCells(xlCellTypeVisible)
.AutoFilter

End With


Columns("R").EntireColumn.Delete
rngCopy.Copy ws2.Cells(1, 1)


For Each cell In rngFilter.CurrentRegion.SpecialCells(xlCellTypeVisible).Rows


Next cell


End Sub

Thanks in advance!

Regards
Sreekanth