Excel VBA code needed to copy row if criteria is met

Question

I have an excel document with two sheets. Sheet 1 has columns A-Q and Sheet 2 has columns A-H. What I need is a code that will copy the information in a row from sheet 1 to sheet 2 if the criteria is met. The criteria is the word "Awarded" in column L (Sheet 1). Also is it possible to have only specific columns in the row copied?

A    B    C    D    E    F    G    H    I   J   K   L      M   N
          X    X                                  Awarded  X   X

I would like to have only columns C,D,M, and N copied from the row if the word "awarded" is in column L. This information would be copied to Sheet 2 in the following fashion

Sheet 1       Sheet 2  
D        -->   B  
C        -->   C  
M        -->   D  
N        -->   F

I hope I'm being clear. Thanks in advance and let me know if I need to clarify!+

This is the code I currently have, which works. Only problem is it copies the entire row of information into sheet 2 when I only want rows D,C,M, and N to be copied.

Sub testing()
Set a = Sheets("Sheet1")
Set b = Sheets("Sheet2")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(a.Range("L" & j))



 If a.Range("L" & j) = "Awarded" Then
 d = d + 1
 b.Rows(d).Value = a.Rows(j).Value

 End If
 j = j + 1

Loop
End Sub

[THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) will get you started. — Siddharth Rout, Aug 16 '16 at 15:40
So far I tried an =IF formula which worked but had issues when I had multiple rows meet the criteria. The formula would keep duplicating the line of data with the first line that met the criteria. — Rami, Aug 16 '16 at 16:28
Also I'm not looking to copy the entire row. Only columns D,C,M, and N in that row. The rest of the columns I do not want copied into sheet 2. Other codes that I found worked but copied the entire row of data. — Rami, Aug 16 '16 at 16:30
Loop through all rows in Sheet1. If column L is “awarded”, then copy C, D, M, N values in Sheet1 to B, C, D, F in Sheet2 (something like `Worksheets(“Sheet2”).Cells(k, 2).Value= Worksheets(“Sheet1”).Cells(i, 3).Value`; i for the loop of Sheet1 data, k for tracking rows in Sheet2. (This for copying Column C (col #3) of Sheet1 to B (col #3) in Sheet2). — CMArg, Aug 16 '16 at 16:50
Without formulas or code, this is likely to get downvoted as not being about programming. — zhon, Aug 16 '16 at 18:06
Okay I posted the code I've been using. Thanks for the heads up zhon. — Rami, Aug 16 '16 at 18:22
Instead of `b.Rows(d).Value = a.Rows(j).Value` try `b.Cells(d, 3).Value = a.Cells(j, 5).Value` (adjust column numbers to fit your needs). — CMArg, Aug 16 '16 at 18:35
@CMArg Thanks for that! So would I need to type out multiple lines of that code to have the other columns included? I need 4 columns copied from the first sheet to the second if the criteria is met. Also is there a way to start the code at a different row? Because my next action would be to have those same columns copied at a lower row on sheet 2 if the word "Passed, Not Funded" was in column L. — Rami, Aug 16 '16 at 18:47
1) yes, you have to type multiples lines, unless you can come up with a regularity, and make it a loop: `for i=1 to 4`, and then `b.Cells(d, i+2).Value=a.Cells(j, i+8).Value`. — CMArg, Aug 16 '16 at 18:51
2) Don't understand you. But maybe you can just write `j=j+2` on "Passed, Not Funded" condition. — CMArg, Aug 16 '16 at 18:54
Okay so let me try to explain. On sheet 2: Rows 1-20 will be for "Passed" Rows 21-30 will be "Passed, Not Funded". How would I set the code to start copying information on row 21 rather than row 1? — Rami, Aug 16 '16 at 19:00
Really can't get what you want. Copied information starts in row 1 because you're setting `d=1` after Dim. Then, if "Awarded" is found, you're increasing d to next row (`d=d+1`). Please explain your problem better. — CMArg, Aug 16 '16 at 21:25
You just answered my question. So if I set d=20 then once my criteria is found, data will be copied to row 21. Perfect thank you! — Rami, Aug 18 '16 at 14:53

SandPiper · Answer 1 · 2016-08-16T18:45:53.097

First what you should do is change your data structure. Assuming you are using Excel 2007 or later, there is a great feature called Tables. If you highlight all of your data and go to Insert->Table, select the "My Table Has Headers" checkbox, and press ok, you will see a nicely formatted table. Do that for both of the data sets on each sheet.

This is more than just pretty formatting though, it is what is called a ListObject. In your VBA code, use the following to reference it:

Dim Table1 as ListObject, Table 2 as ListObject
Dim HeaderIndex as Integer
Dim MyColumnRange as Range

Set Table1 = Sheet1.ListObjects("TableName1") 
    `Change the table name under Formulas->Name Manager
Set Table2 = Sheet1.ListObjects("TableName2") 

HeaderIndex = Application.WorksheetFunction.Match("ColumnLHeaderName", _ 
    Table1.HeaderRowRange, 0)
Set MyColumnRange = Table1.ListColumns(HeaderIndex).DataBodyRange
MyColumnRange.Select

At this point, the select statement is just to show you what range you are dealing with now. The HeaderIndex refers to the header sub component of the table ListObject. Using Match() will allow you to specify the name of the column header without hard coding it's position. (i.e. if your data starts in column A, the header value in column L will return HeaderIndex = 12)

Now that you know what column you want, you select the ListColumn object. Then, the DataBodyRange is used to select the range component of that object. This is the entire range in that column. You can then iterate down the list to find the data you want.

EDIT: Updated Example:

'Specify your ranges you will be copying from beforehand, adding as many as you need here.
HeaderIndex_D = Application.WorksheetFunction.Match("ColumnXHeaderName", _ 
    Table1.HeaderRowRange, 0)
HeaderIndex_C = Application.WorksheetFunction.Match("ColumnXHeaderName", _ 
    Table1.HeaderRowRange, 0)
HeaderIndex_M = Application.WorksheetFunction.Match("ColumnXHeaderName", _ 
    Table1.HeaderRowRange, 0)
HeaderIndex_N = Application.WorksheetFunction.Match("ColumnXHeaderName", _ 
    Table1.HeaderRowRange, 0)
Set ColumnRange_D= Table1.ListColumns(HeaderIndex_D).DataBodyRange
Set ColumnRange_C= Table1.ListColumns(HeaderIndex_C).DataBodyRange
Set ColumnRange_M= Table1.ListColumns(HeaderIndex_M).DataBodyRange
Set ColumnRange_N= Table1.ListColumns(HeaderIndex_N).DataBodyRange


'Now, loop through each row that exists in your table. If the testing 
'condition contained in MyColumnRange you previously defined is met,
'then assign the destination cell (which can be defined in the same way
'as above) equal to the lookup range's current row value (specified by i)
For i = 1 to MyColumnRange.Rows.Count
    If MyColumnRange(i) = "Awarded" Then
        DestinationCell1.Value = ColumnRange_D(i)
        DestinationCell2.Value = ColumnRange_C(i)
        DestinationCell3.Value = ColumnRange_M(i)
        DestinationCell4.Value = ColumnRange_N(i)
    End If
Next i

Thanks for this! This was actually very useful as I originally started with all my data being in a table, but found that codes were not working for it until I removed the table. So with the code you've written out is there a way to make the If statement copy only specific columns from the row if the criteria was met? Thanks. — Rami, Aug 16 '16 at 18:16
@Rami, see my edit. Essentially, you define your ranges you will be copying from and too, then as you loop down the rows, assign the values that are referenced by the loop index (in this case, i). I hope that helps! Don't forget to accept the answer if it does :) — SandPiper, Aug 16 '16 at 18:30
So would " Table1.HeaderRowRange, 0)" be changed to "Table1.headRowRange,50)" if I had 50 rows? Also what line in the code specifies which columns to be copied if the criteria is met? I would need to have 4 columns copied if the criteria is met. For this line of code : "For i = 1 to MyColumnRange.Rows.Count If MyColumnRange(i) = "MySpecification" Then" Is the words "MySpecification" the criteria for copying the other columns in the row? Thanks for your patience. I just started using VBA two days ago. — Rami, Aug 16 '16 at 18:36
@Rami, the 0 in the Match() statement is a enum that tells it to give you an exact match, not a close match. Leave that one alone. — SandPiper, Aug 16 '16 at 18:40
@Rami, where I said "MySpecification", yes, that is what you need to change to be what you want. For example, if you want to only copy the data if that column entry is "Awarded", then you would say: If MyColumnRange(i) = "Awarded" Then ... Alternatively, you can use a fixed range value: If MyColumnRange(i) = Range("A1") — SandPiper, Aug 16 '16 at 18:43
@Rami, I updated the code again. I hope you can see what I am doing now with defining Table1 Ranges. I didn't want to make the example any longer than it already is, but you repeat the pattern with Table2 and replace DestinationCellX with the names of your Table2 columns. Example: ColumnRange_DestinationX(j) = ColumnRange_D(i), where j is the row index of the table you are copying to. — SandPiper, Aug 16 '16 at 18:49
And how would I change the code to have it copy the data in columns D,C,M, and N if the criteria was met? And not just that but to also have those columns copied to specific columns in sheet 2 (B,C,D, and F)? — Rami, Aug 16 '16 at 18:50
@Rami, The info as all there. At the top it tells you how to set up tables and define them in your module. In the 2nd code block, copy the lines at the very beginning and paste right below them, but change the Table1 to Table2, and the column labels to what you want. Then you can change in the for loop: ColumnRangeTable2_C(j) = ColumnRangeTable1_C(i). You just have to figure out what your j index is (i.e. how many records do you have in Table2, and what is the next one?) Percolate on it for a little while, it will start to sink in. I only learned VBA a few months ago myself, I understand :) — SandPiper, Aug 16 '16 at 18:59
Okay so I've gone through the code a little closer now and I see how table 1 columns D,C,M, and N are defined. However, I'm not seeing anything about columns B,C,D, and F for table 2. Is it written in a different way? — Rami, Aug 16 '16 at 19:14

Excel VBA code needed to copy row if criteria is met

1 Answers1