Copy rows based on a conditionSolved/Closed

Question

Hi

How can I have excel copy the entire row of data from worksheet Employee Inventory to another worksheet called EEs if column Q contains TERM.  I've tried almost everything but nothing is working.  Please help!

Helper · Accepted Answer

Assumptions for this code:
1. Employee Inventory sheet has heading in row one so it will start checking the data in row 2.
2. Column Q does not have any empty cells.
3. EEs sheet will have headings in row one so the data will start copying in row 2.

Set I = Sheets("Employee Inventory")
Set e = Sheets("EEs")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("Q" & j))

If i.Range("Q" & j) = "TERM" Then
    d = d + 1
    e.Rows(d).Value = i.Rows(j).Value
    
    End If
    j = j + 1
Loop

MattG · Answer

This code is really helpful!

I'm having problems using it to copy and paste using more than one IF statement.

For example...

Set a = Sheets("Sheet1")
Set b = Sheets("Sheet2")
Set c = Sheets("Sheet3")
Dim x
Dim z

x = 1
z = 2

Do Until IsEmpty(a.Range("B" & z))

If a.Range("B" & z) = "TERM" Then
x = x + 1
b.Rows(x).Value = a.Rows(z).Value

Else

If a.Range("B" & z) = "ACTIVE" Then
x = x + 1
c.Rows(x).Value = a.Rows(z).Value

End If
End If
z = z + 1
Loop
End Sub

The code does work but doesn't remove the blank lines between the copied data - how do I remove these?

Many thanks

IanO · Answer

To delete the blanks, just select the whole sheet and sort the data by the column that may have blanks.

Is there a way of copying for formatting across when using the rowns=rows format ?

Sub CLEANUP_S2()
'
' CLEANUP_Sheet2 Macro
'

'
    Cells.Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("F2:F9999") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Rows("2:9999")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.NumberFormat = "@"
    Columns("F:F").Select
    Selection.NumberFormat = "General"
    Cells.Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("F2:F9999") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Rows("2:9999")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Range("A1").Select
End Sub

Mike · Answer

I have moved a load of excel files from an old laptop to a new laptop and have lost the macro. on one spreadsheet each line comprised a company name with invoice breakdown for VAT, and then by left clicking on the cell containing the company name and then pressing   control and L   it would copy and paste the line into another spreadsheet which was a ledger card for that company. the company ledger would then add the totals of the invoices and show the total owed to that company.


path to open vat file :- 
                    Libraries - Documents - vat files - vat file Acorn (business name)

Left clicking on the company invoice name being entered and Pressing Control + L
transferred ( presumably by copy and paste ) all the cells from A to K were transferred to the relevant company ledger card.

Path to open company ledger :-
                 Libraries - Documents - Excel - Ledger cards -company name ledger.

The original spreadsheets on the old computer were on excel 2003 and the new laptop is on excel 2010, but I believe files can be saved on the new laptop in excel 2003.

Can anybody write a Macro to do this transfer from one page of excel to another please.The old macro comprised about 6 or 7 lines.

Edward · Answer

Hi folks!

I had the same problem as posted by Blossom83 on Oct. 14, 2008, that started this discussion. I solved it by applying the solution posted by Helper on Febr. 2, 2009 (thanks Helper!). 
One problem remains for me, however: how can I, using Helper's solution, also copy the cell borders from the source file into the receiving file? Appreciate any suggestions.

Edward · Answer

Sorry, my apologies. This was already answered. Hallelujah! Thanks Rizvisa1!

Joel · Answer

Hi,

What if I only need selected columns from a row to be copied next to each other, say columns B, D, E, G. What needs to change with the below?



Assumptions for this code: 
1. Employee Inventory sheet has heading in row one so it will start checking the data in row 2. 
2. Column Q does not have any empty cells. 
3. EEs sheet will have headings in row one so the data will start copying in row 2. 

Set I = Sheets("Employee Inventory") 
Set e = Sheets("EEs") 
Dim d 
Dim j 
d = 1 
j = 2 

Do Until IsEmpty(i.Range("Q" & j)) 

 If i.Range("Q" & j) = "TERM" Then 
 d = d + 1 
 e.Rows(d).Value = i.Rows(j).Value 

 End If 
 j = j + 1 
Loop

Hunter4573 · Answer

what do the lower case letters in the code represent?

Set I =
Set e =
Dim d
Dim j

I am using this code but want to auto-populate into 3 worksheets from one master sheets based on 3 different conditions.

Code works great for first worksheet but gives me duplicate warnings when I try repeat it.

If I knew what i,e,d,j represent I would be able to adjust accordingly for the other 2 worksheets.
Thanks

Copy rows based on a condition

8 responses

Similar discussions

Subscribe To Our Newsletter!