0

I've put in the autofilter code to specify the criteria for the copypaste action but not sure whether i've done it correctly or not.

Sub test3()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim copyFrom As Range
Dim lRow As Long
Dim strSearch As String


Set ws1 = Worksheets("sheet1")

strSearch = "LOCAL"

With ws1

    '~~> Remove any filters
    .AutoFilterMode = False


    lRow = .Range("L" & .Rows.Count).End(xlUp).Row

    With .Range("L4:L" & lRow)
        .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
        Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
    End With

    '~~> Remove any filters
    .AutoFilterMode = False
End With

'~~> Destination File

Set ws2 = Worksheets("Sheet2")

With ws2
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lRow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lRow = 1
    End If

    copyFrom.Copy .Rows(lRow)
End With

Worksheets("Sheet2").Columns().AutoFit
Cells(1, 1).Activate

End Sub

It is very much appreciated if anyone could highlight what went wrong with the code that i use. Thanks.

Community
  • 1
  • 1
hanaa
  • 3
  • 3
  • Please shed some light about what really went wrong? – Romcel Geluz May 29 '17 at 06:19
  • 1
    Have you seen [This](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) especially the part `Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow`? – Siddharth Rout May 29 '17 at 06:24
  • 1
    @SiddharthRout welcome back ;) where have u been ? Vacationing ;) – Shai Rado May 29 '17 at 06:31
  • Side note: Always use `Long` instead of `Integer` unless communicating with old APIs. Excel has more rows than `Integer` can handle. – Pᴇʜ May 29 '17 at 06:36
  • @RomcelGeluz The data is filtered, however, the copypaste action take all the data instead of taking the filtered data only. – hanaa May 29 '17 at 06:49
  • @ShaiRado: Nah. Back injury + Burnout etc... :D – Siddharth Rout May 29 '17 at 06:49
  • 1
    @SiddharthRout I'll go thru the link. Thx for sharing. – hanaa May 29 '17 at 06:50
  • @SiddharthRout I've take a look link provided. however, in my case, not every column need to be copied to the new sheet. There are selective number of columns need to be pasted in the new sheet which didn't work as the code i used above. – hanaa May 29 '17 at 08:12
  • The `copyFrom` in `Set copyFrom...` is the visible range after the autofilter is applied. Simply copy the relevant columns from `copyFrom` to the other sheet :) – Siddharth Rout May 29 '17 at 08:27
  • @SiddharthRout I'm still new to vba and not really sure how to do it. Could you show me?. For example, from the filtered data, i need to copy column B n C to column F n G of the new worksheet. Thx for assistance. – hanaa May 30 '17 at 02:57
  • I will. :) First update your question with the code where you reach the autofilter part as I have shown you in that post and then we will take it fromthere – Siddharth Rout May 30 '17 at 03:56
  • @SiddharthRout I've edited the code above. I'm using the sample you gave and modified to suit mine. The filter criteria in column L and i need to copy selective columns to different worksheet in different range. For example, copy column B n C to column F n G of the new worksheet. Your assistance is very much appreciated. – hanaa May 30 '17 at 05:14
  • I am stepping out for couple of hours. Need to pick my mom from the airport. Will look at it once I am back :) – Siddharth Rout May 30 '17 at 05:18
  • @SiddharthRout Noted. Thanks. :) – hanaa May 30 '17 at 05:23

0 Answers0