VBA Excel filter data and copy to another worksheet - Newbie alert

Question

Hi I am a complete newbie when it comes to VBA Excel.

Im trying to filter a variable sized spreadsheet full of data looking for a set word in one column. Once found I want to copy the complete row to another worksheet.

As they maybe more than one row with this word in it I don't want to write over the previous copied row.

this is what ive created to find the word, but how do I copy the row to another worksheet?

Sheets("Sheet1").Select                                 'Select datasheet
Range("A1").Select                                      'Set cell position to start search from

Do Until Selection.Offset(0, 4).Value = ""              'word to be searched is 4 cell in
                                                        'do what is required
    If Selection.Offset(0, 4).Value = "UKS" Then
        MsgBox "Found"                                  'Found it!
        'not sure how to copy row to another worksheet
    End If

'finish move on to next one in list
         Selection.Offset(1, 0).Select                  'move down 1 row
    Loop

    Range("A1").Select ' reset cell position

Any help would be appreciated, please could you explain how it works as well as I like to understand rather than just copying.

Jason

Answer 1

Have a look down-below, perhaps it gives you clue. Nevertheless, I will try to comment the details to get you acquainted closely with the matter.

My example is a bit more complicated because it applies filtering on two columns at once, but it is good for you understand it as complicated as it is because you can further apply it.

 With Sheets("Source")
            .AutoFilterMode = False
        With .Range("$A$21" & ":" & "$C$" & 300)
             .AutoFilter Field:=1, Criteria1:=Array("April", "August", "Dezember", "Februar", "Januar", "Juli", "Juni", "Mai", "März", "November", "Oktober", "September"), Operator:=xlFilterValues
             .AutoFilter Field:=2, Criteria1:="<>"
             ActiveSheet.AutoFilter.Range.Copy
             Sheets("Chart").Select
             Range("A7").Select
            Sheets("Chart").Paste
         End With
        End With

So, what is this about:

logic of the code is

• filter on two columns columns 1 by the name of the Months (i.e. German language), column 2 filter the elements by eliminating the cells which are blanks/empty

With Sheets("Source") .AutoFilterMode = False With .Range("$A$21" & ":" & "$C$" & 300) .AutoFilter Field:=1, Criteria1:=Array("April", "August", "Dezember", "Februar", "Januar", "Juli", "Juni", "Mai", "März", "November", "Oktober", "September"), Operator:=xlFilterValues

• name of the Worksheet that contains the Table to apply the filtering upon is "Source". leave .Autofiltering to false

  .AutoFilterMode = False

• the filtering values are set as follows: column 1

.AutoFilter Field:=1, Criteria1:=Array("April", "August", "Dezember", "Februar", "Januar", "Juli", "Juni", "Mai", "März", "November", "Oktober", "September"), Operator:=xlFilterValues

column 2

.AutoFilter Field:=2, Criteria1:="<>"

• last, but not least (very important) is the Range ( the cells of the tables to which the filtering is applied)

With .Range("$A$21" & ":" & "$C$" & 300)

in this case my example applies the filter to a range of cells( as a matter a fact a table) that starts with A21 and ends up at C300.

why A21? Because that's where my data gets copied. It starts from A21 always.

why C300? Because the maximum number of rows will never exceed (300-21)=279 rows my model of data is not exceeding 279 of unfiltered rows at any time, you can put a greater figure as per your assumption. If there are more rows, nevermind because I filtered them by eliminating the blanks , see above.

By the way, the "kosher" version is to count the number of rows via VBA and use this when defining your range.

You can simply use a very large number that covers the possible number of rows in your table.

Counting the number of rows this might be a bit complicated for you at the 1st glance, but shall pay its pennies in the end.

supposedly you want to count the number of rows (variable) in Column B FinalRowChartSheet = Range("B7").End(xlDown).Row

Wish you the best. I hope I helped you.

Do not forget to rate my answer if you find it useful.Thank you.

Answer 2

Here is a loop code example and a filter code example.

Sub loopMe()

    Dim sh As Worksheet, ws As Worksheet
    Dim LstR As Long, rng As Range, c As Range

    Set sh = Sheets("Sheet1")    'set the sheet to loop
    Set ws = Sheets("Sheet2")    'set the sheet to paste
    With sh    'do something with the sheet
        LstR = .Cells(.Rows.Count, "D").End(xlUp).Row    'find last row
        Set rng = .Range("D2:D" & LstR)    'set range to loop
    End With

    'start the loop
    For Each c In rng.Cells
        If c = "UKS" Then
            c.EntireRow.Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)    'copy row to first empty row in sheet2
        End If
    Next c

End Sub

Sub FilterMe()
    Dim sh As Worksheet, ws As Worksheet
    Dim LstR As Long, rng As Range

    Set sh = Sheets("Sheet1")    'set the sheet to filter
    Set ws = Sheets("Sheet2")    'set the sheet to paste
    Application.ScreenUpdating = False
    With sh    'do something with the sheet
        LstR = .Cells(.Rows.Count, "D").End(xlUp).Row    'find last row
        .Columns("D:D").AutoFilter Field:=1, Criteria1:="UKS"
        Set rng = .Range("A2:Z" & LstR).SpecialCells(xlCellTypeVisible)    'Replace Z with correct last column
        rng.Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilterMode = False
    End With

End Sub