Geek Answers Handbook

Vba macro to copy a row from a table if the value in the table matches the condition

I am trying to make a macro that:

  • goes through the table
  • looks if the value in column B of this table has a specific value
  • if it is, copy this line to a range in another sheet

The result is similar to filtering a table, but I want to avoid hiding any rows

I am new to vba and don't know where to start, any help is much appreciated.

+4
source share
4 answers

This is exactly what you do with the advanced filter. If this is one shot, you don’t even need a macro, it is available in the "Data" menu.

Sheets("Sheet1").Range("A1:D17").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Sheet1").Range("G1:G2"), CopyToRange:=Range("A1:D1") _ , Unique:=False
+5
source

Chooses slow and unescsaary. The following code will be much faster:

 Sub CopyRowsAcross() Dim i As Integer Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1") Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2") For i = 2 To ws1.Range("B65536").End(xlUp).Row If ws1.Cells(i, 2) = "Your Critera" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1) Next i End Sub
+2
source

Try the following:

 Sub testIt() Dim r As Long, endRow as Long, pasteRowIndex As Long endRow = 10 ' of course it best to retrieve the last used row number via a function pasteRowIndex = 1 For r = 1 To endRow 'Loop through sheet1 and search for your criteria If Cells(r, Columns("B").Column).Value = "YourCriteria" Then 'Found 'Copy the current row Rows(r).Select Selection.Copy 'Switch to the sheet where you want to paste it & paste Sheets("Sheet2").Select Rows(pasteRowIndex).Select ActiveSheet.Paste 'Next time you find a match, it will be pasted in a new row pasteRowIndex = pasteRowIndex + 1 'Switch back to your table & continue to search for your criteria Sheets("Sheet1").Select End If Next r End Sub
+1
source

You describe a problem that I will try to solve using the VLOOKUP function, and not using VBA.

You should always consider a solution other than vba first.

Here are some examples of VLOOKUP applications (or SVERWEIS in German, as I know):

http://www.youtube.com/watch?v=RCLUM0UMLXo

http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

If you need to do this as a macro, you can use VLOOKUP as an application function - a quick solution with low performance - or you will have to execute the simillar function yourself.

If it should be the last, then you will need more details about your specification regarding performance issues.

You can copy any range into an array, go through this array and check its value, and then copy this value to any other range. This is how I would solve it as a vba function.

It will look something like this:

 Public Sub CopyFilter() Dim wks As Worksheet Dim avarTemp() As Variant 'go through each worksheet For Each wks In ThisWorkbook.Worksheets avarTemp = wks.UsedRange For i = LBound(avarTemp, 1) To UBound(avarTemp, 1) 'check in the first column in each row If avarTemp(i, LBound(avarTemp, 2)) = "XYZ" Then 'copy cell targetWks.Cells(1, 1) = avarTemp(i, LBound(avarTemp, 2)) End If Next i Next wks End Sub

Ok, now I have something nice that can come in handy for me:

 Public Function FILTER(ByRef rng As Range, ByRef lngIndex As Long) As Variant Dim avarTemp() As Variant Dim avarResult() As Variant Dim i As Long avarTemp = rng ReDim avarResult(0) For i = LBound(avarTemp, 1) To UBound(avarTemp, 1) If avarTemp(i, 1) = "active" Then avarResult(UBound(avarResult)) = avarTemp(i, lngIndex) 'expand our result array ReDim Preserve avarResult(UBound(avarResult) + 1) End If Next i FILTER = avarResult End Function

You can use it in your sheet, for example: FILTER (Tabelle1! A: C; 2) or with = INDEX (FILTER (Tabelle1! A: C; 2); 3) specify the result line. I'm sure someone can extend this to include index functionality in FILTER or know how to return a range as an object - maybe I could, too, but not today;)

0
source

More articles:


All Articles