Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Copy rows based on a condition [Solved/Closed]

Report
-
Posts
1
Registration date
Wednesday December 12, 2018
Status
Member
Last seen
December 12, 2018
 -
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!
Related:

8 replies

Answer 1 / 8

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
91
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

View all 11 comments
Report

In Visual Basic under the Developer Tab
Report
Posts
1
Registration date
Tuesday December 13, 2016
Status
Member
Last seen
December 13, 2016
Is there any way to copy only some columns instead of all? Please let me know. I tried adding a Columns() to this but it only returns one row.

e.Rows(d).Value = i.Rows(j).Value
Report

Does anybody know how to change the "TERM" query to a cell location on another sheet. Eg Sheet2!A1? 

Sub CopyAlertDate()
Set i = Sheets("Sheet1")
Set e = Sheets("Sheet 2")
Dim d
Dim j
d = 1
j = 2

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

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

End If
j = j + 1
Loop
End Sub


Thanks
Report

thank you very much. it is exactly what i was looking for.
Report
Posts
1
Registration date
Wednesday December 12, 2018
Status
Member
Last seen
December 12, 2018
Sir,

My requirement is same. instead of 'TERM' i need to use a column value. could you please let me know how to do this?
Answer 2 / 8

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
Answer 3 / 8

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
View all 7 comments
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
this is the quick of copying the values. if you want formatted text to be copies (not the format) then replace
e.Rows(d).Value = i.Rows(j).Value
by
e.Rows(d) = i.Rows(j).TEXT

If you want to copy both values and format, then you have to use copy and paste

i.select
Rows(j).copy

e.select
rows(d).paste
Report

Would this copy the cell format (ie Borders) aswell as the text format (ie. Bold, Italics)
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
it should copy all
Report

If I use ...
Sub test()
Set a = Sheets("Sheet1")
Set b = Sheets("Sheet2")
Set c = Sheets("Sheet3")
Dim x
Dim z

x = 1
z = 2

Do Until z = 9999

If a.Range("X" & z) = "N" Then
x = x + 1
'b.Rows(x).Value = a.Rows(z).Value
a.Select
Rows(z).Copy

b.Select
Rows(x).Paste


I get the error:
Run-Time error '438'
Object doesn;t support this property or method.

Using the .TEXT method mention above nothing gets copied.

Any thoughts ?
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
Change this line

Rows(x).Paste to Rows(x).PasteSpecial
Answer 4 / 8

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.
Answer 5 / 8

Sorry, my apologies. This was already answered. Hallelujah! Thanks Rizvisa1!
Answer 6 / 8

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
Report

what does the word "TERM" represent
Report
> OQY
TERM is what the person asking the original question wanted to use as their specific condition.
Anything from worksheet 1 that had a cell with TERM written in it was to be transferred to worksheet 2
Report

Hello,

How to code macro on the basis of status. For Eg. in Sheet one there is status of Yes & No in Column B and students name in column A. Now i want that names of student should be automatically populated in sheet 2 for names on the basis of yes. If any changes is made in sheet one for status yes and name should should be populated in sheet 2.
Answer 7 / 8

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.
Answer 8 / 8
Posts
1
Registration date
Tuesday April 19, 2016
Status
Member
Last seen
April 19, 2016
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
Report

Hi,

I am after some help. A bit new to Excel and only have basic skills. I am currently setting a database up and need the data on my main worksheet (Data Tab) to automatically fill out the other works sheets. I Need it to copy across based on the week number the data was entered. The format of all the sheets are the same.

The Week Number is in column A. and I have got data relating to that entry in Columns B to I.

Sheet Names I want to copy to our Week 44 right through to Week 52

Please Help
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!