copy entire rows to another sheet
 

Hi Guyz!!!
I have a worksheet having different column for e.g
Column A (CODE) Contains - WC1, WC2, WC3
Column B (CATEGORY) Contains - PL, SA1, SA2
Column C (QUANTITY) Contains - 100, 200, 300,
Column D (UNITS) Contains - KG, ML, NO
When i run a macro first it should ask to enter CODE (where i will enter WC1) where the worksheet will filter all the rows which contains WC1.
Then by click GENERATE button it should copy entire row to another sheet which contains PL in it.
Please if anybody can help me on this...Thanx

Attached is the spreadsheet with sample vba code. click the button in the attached spreadsheet, result will be copied to new sheet at the left side.


'created by Bala Sesharao

Private Sub CommandButton1_Click()

Dim a, b As Variant
a = InputBox("enter the CODE in the box")

Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$10000").AutoFilter Field:=1, Criteria1:="=" & a, _
Operator:=xlAnd

Cells.Select
Selection.Copy
Sheets("Main").Select
Sheets.Add
ActiveSheet.Paste
Sheets("Main").Select
Range("A1").Select
Selection.AutoFilter
End Sub

all the best

Thanks for your concern Bala, we are not through to what we need but very near to it.
When i enter code eg. WC1, all the rows that contains WC1 gets copied to a a new Sheet 1.
Now, i need a code which gives me an option of choosing PL or SA1 OR SA2 in Sheet 1, which should also copy rows that contains one of these options to Sheet 2.

Thanx, for your concern once again awaiting for your earliest reply..