Copy Rows to a new sheet on criteria

[niraj4]

Hi I have table:

Name Amount Category City Comments
Nitin 10039 A Paris Good boy
Jack 292 A Brussels Bad Boy
Murphy 28321 B Bristol doesn't do his homework
Bob 32432 B Bristol looks ok
Williams 23892 B Brussels talks a lot
Jenny 19392 A Bristol makes a mess
Lenny 7942 A Paris happy
Kenny 290 A Paris don’t know much
Liza 9874 B Bristol doesn't want to come
Ram 527 A Bristol has a problem
Marc 39287 A Paris fights all the time
John 4883 B Brussels may do good
Stephen 97205 B Paris immitates teacher
Joshua 3684 A Bristol who is he
Marlyn 746 B Bristol Parents called
Moose 479 A Brussels sobre
Betty 837 B Brussels in love


I want a popup box which asks for the category and then another popup box which asks for the city and then copies all the rows with the category and city entered via popup box to another sheet (Sheet2).

Example: In the popup I enter Category = A and City = Paris, then the following row should get copied to the Sheet2

Nitin 10039 A Paris Good boy
Lenny 29942 A Paris happy
Kenny 290 A Paris don’t know much
Marc 39287 A Paris fights all the time

Regards,
Niraj

 

[ravishankar]

Hi
try the following macro codes

Sub Macro1()
Dim a As Long, b As Long, d As Long, z As Long
Dim x As String, y As String
d = 2
x = InputBox("enter category")
y = InputBox("Enter city")
z = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To z
If Sheets("sheet1").Cells(a, 2) = x And Sheets("sheet1").Cells(a, 4) = y Then
Sheets("sheet1").Range("A" & a & ":E" & a).Copy
Sheets("sheet1").Range("A" & d).PasteSpecial
d = d + 1
End If
Next a
MsgBox "complete"
End Sub

Ravi

 

[niraj4]

Thanks Ravi!

I get an error at the line

Sheets("sheet1").Range("A" & a & ":E" & a).Copy

Run-time error '9'
Subscript out of range

Actually I have 31 columns so I had replace :E by :AE. Could you please help ?