Santed593 Wrote:
I need help to create a vba code or macro to filter data based on input
box entry. In my worksheet I have 110 rows of Promo Titles. In each
column (200 columns) are dollar sales goals of promo titles by Account
No. This means that the column headings are Account No. I would to
prompt the user using the input box to enter the Account No and the
macro will filter the dollar sales goals for all promo titles of that
Account No. Once filtered I would like to copy and paste the info to a
new worksheet. I have searched and read a lot of posting about data
filtering but I can’t find anything about row filtering based on column
input box entry. Any help is greatly appreciated.

Below is the code written:

Sub Filter_Cust()
strCriteria = InputBox("Enter Criteria")
If strCriteria = vbNullString Then Exit Sub

'Create a Worksheet with the customer name and copy over all data
Sheets("Distr").Select
Sheets("Distr").Copy Befo=Sheets(1)
Sheets("Distr (2)").Select
Sheets("Distr (2)").Name = strCriteria


Range("B1").Select

'Look for the customer data
Do Until ActiveCell.Value = ""
If Trim(ActiveCell.Value) = strCriteria Then
ActiveCell.Offset(0, 1).Range("A1").Select
Else
Selection.EntireColumn.Delete
End If
Loop

'Check to see if customer was found
If ActiveCell.Column = 2 Then
Sheets(strCriteria).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
MsgBox "Customer not found"
End If

End Sub

Any suggestion to cleanup the above code.


--
Santed593
------------------------------------------------------------------------
Santed593's Profile: http://www.excelforum.com/member.php...o&userid=25108
View this thread: http://www.excelforum.com/showthread...hreadid=394362