Menu
Ask a question Report

Copy rows based on a condition [Solved/Closed]

- - Latest reply: ravichennai86
Posts
1
Registration date
Wednesday December 12, 2018
Last seen
December 12, 2018
 - Dec 12, 2018 at 08:00 AM
Excel Ask a question Report
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!
See more 

8 replies

Best answer
approved by Jean-François Pillou on Dec 1, 2018
Report Respond to Helper
88
Thank you
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

Say "Thank you" 88

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2968 users have said thank you to us this month

View all 11 comments
Report
-
In Visual Basic under the Developer Tab
Report
sharatdotinfo
Posts
1
Registration date
Tuesday December 13, 2016
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 Respond to ravichennai86
ravichennai86
Posts
1
Registration date
Wednesday December 12, 2018
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?
Report Respond to MattG
23
Thank you
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
Report Respond to IanO
7
Thank you
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
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 -
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
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 -
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
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 -
Change this line

Rows(x).Paste to Rows(x).PasteSpecial
Report Respond to Edward
4
Thank you
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.
Report Respond to Edward
4
Thank you
Sorry, my apologies. This was already answered. Hallelujah! Thanks Rizvisa1!
Report Respond to Joel
1
Thank you
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.
Report Respond to Mike
0
Thank you
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.
Report Respond to Hunter4573
Posts
1
Registration date
Tuesday April 19, 2016
Last seen
April 19, 2016
0
Thank you
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