Filter based on Range and copy paste

I have sample data, which contains sheet name range.in that sheet based Item, I need to Filter the Data in sheet 1 and copy paste to the specific sheet name automatically-Kindly do help me.

Acess Denied

Does this help?

Sub kannan1847()
Dim x As String
x = InputBox("Please Enter the Filter Criteria")
Sheets(x).Rows(1).Value = Sheets("Sheet1").Rows(1).Value
With Sheets("Sheet1")
    .Range("A1:A" & .Range("A" & Rows.Count).End(3).Row).AutoFilter 1, x
    .Range("A2:A" & .Range("A" & Rows.Count).End(3).Row).SpecialCells(12).EntireRow.Copy Sheets(x).Range("A" & Rows.Count).End(3)(2)
    .AutoFilterMode = False
End With
End Sub

JOHN H. DAVIS






Hi Mr. JOHN H. DAVIS

Thanks it works
but I need all filter criteria automatically without msg box

Try:

Sub kannan1847()
Dim i As Long
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Sheet1" And Sheets(i).Name <> "Range" Then
        Sheets(i).Rows(1).Value = Sheets("Sheet1").Rows(1).Value
            With Sheets("Sheet1")
                .Range("A1:A" & .Range("A" & Rows.Count).End(3).Row).AutoFilter 1, Sheets(i).Name
                .Range("A2:A" & .Range("A" & Rows.Count).End(3).Row).SpecialCells(12).EntireRow.Copy Sheets(i).Range("A" & Rows.Count).End(3)(2)
                .AutoFilterMode = False
            End With
        End If
    Next i
End Sub

super it works thaks

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rules and mark this thread as solved. See link for guidance.

http://www.excelforum.com/forum-rule...rum-rules.html