Copy Visible Cells after filter if no data don't copy

[harinsh]

Hi Team,

I know this is something can get it online but I could not get it and leads to lots of confusion. I need help in copying visible cells.

For Example I have columns from A to AE and want to filter something on column AE and code should select only visible cells post filtered and copy only those cells excluding column header and in case no data found after filter then code should not copy and end the condition.

Could you please help me with solution.

Thank you,

 

[My Aswer Is This]

You should tell us exactly what your wanting to do.

Sounds like you have some code your using but it is not working for you and you need help.

We need to know what you want to filter and when you copy the filtered data where do you want to paste it.

 

[harinsh]

Hello,

I am looking for how to select visible cells and copy & paste and if no data found then it should not copy the cells. Currently my code copying header if there is no data.

Let me give with example: Assume you have below data in sheet 1 and if want to filter data which contain column SKU i.e XR23423 then I will get couple of rows which contain this value and want to copy those lines into different sheet assume its sheet 2.

[TABLE="width: 284"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]SKU[/TD]
[TD]DESC[/TD]
[TD]BIN #[/TD]
[TD]LOCATION[/TD]
[/TR]
[TR]
[TD]SP7875[/TD]
[TD]Item 1[/TD]
[TD]T345[/TD]
[TD]Row slot 1[/TD]
[/TR]
[TR]
[TD]TR87680[/TD]
[TD]Item 2[/TD]
[TD]T345[/TD]
[TD]Row slot 1[/TD]
[/TR]
[TR]
[TD]MK676554[/TD]
[TD]Item 3[/TD]
[TD]T5789[/TD]
[TD]Row[/TD]
[/TR]
[TR]
[TD]YE98767[/TD]
[TD]Item 4[/TD]
[TD]T9876[/TD]
[TD]Row slot 2[/TD]
[/TR]
[TR]
[TD]XR23423[/TD]
[TD]Item 5[/TD]
[TD]T098[/TD]
[TD]Row slot 1[/TD]
[/TR]
[TR]
[TD]PW98762[/TD]
[TD]Item 6[/TD]
[TD]T345[/TD]
[TD]Row, slot 1[/TD]
[/TR]
[TR]
[TD]BM87684[/TD]
[TD]Item 7[/TD]
[TD]T349[/TD]
[TD]Row 1, slot [/TD]
[/TR]
[TR]
[TD]BH67655[/TD]
[TD]Item 8[/TD]
[TD]T5789[/TD]
[TD]Row 1, slot [/TD]
[/TR]
[TR]
[TD]WT98768[/TD]
[TD]Item 9[/TD]
[TD]T9875[/TD]
[TD]Row 2, slot [/TD]
[/TR]
[TR]
[TD]TS3456[/TD]
[TD]Item 10[/TD]
[TD]T349[/TD]
[TD]Row 1, slot [/TD]
[/TR]
[TR]
[TD]WDG123[/TD]
[TD]Item 11[/TD]
[TD]T349[/TD]
[TD]Row 1, slot [/TD]
[/TR]
</tbody>[/TABLE]


I hope i clarified you...

 

[My Aswer Is This]

Is there some reason you cannot tell me what column SKU is ?

Can you not say column A or column B

Or does this change all the time?

Or do you want to search column AE

 

[My Aswer Is This]

And will we always be looking for this same value:

XR23423​

If not then we will need a Inputbox where you will enter the search value.

 

[harinsh]

I just given you the Example data ...data will change but logic remains same....not require inputbox because its simple filter the data and copy and paste from sheet 1 to sheet 2 ...its about column only not SKU...I just mentioned header name ...I am looking very basic selection post filter and select only visible cells and paste those values in different sheet....

 

[My Aswer Is This]

Well since you just want a example you can try this and modify to your needs.

Sub Filter_Me_OK()
'Modified 6/24/18 4:00 PM EDT
Dim Lastrow As Long
Dim Lastrowa As Long
Dim c As Long
Dim s As String
Dim ans As String
ans = InputBox("Enter search value")
c = "31" ' Column Number Modify this to your need
s = ans 'Saerch Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, c).End(xlUp).Row + 1
With ActiveSheet.Cells(1, c).Resize(Lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(Lastrowa)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub

 

[harinsh]

..this is exactly what I am looking ....especially below code works perfect ....Thank you very much

counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
If counter > 1 Then
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(Lastrowa)
Else

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

..this is exactly what I am looking ....especially below code works perfect ....Thank you very much

counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
If counter > 1 Then
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(Lastrowa)
Else