Report

Copy rows based on a condition [Solved]

Ask a question Blossom83 - Latest answer on Sep 17, 2016 01:23AM
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 
Helpful
+88
moins plus
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
Anne- Sep 10, 2009 02:01PM
Thanks! That is really helpful to me as well.
Just one more thing: How is it possible to copy data always on the fist empty row on the correspondant sheet (here EES sheet), if this sheet can contain altering number of rows time by time.

Thanks,
Anne
Reply
abhi- Jan 31, 2010 03:08PM
Hi,
I am trying to use this code but its giving me error as Object defined error at do loop.
Can you please help me out
Reply
abhi- Jan 31, 2010 03:14PM
This is my code

Sub search()

Dim hospital_Name As String
hospital_Name = Range("B3").Value
Set I = Sheets("Regional Data")
Set e = Sheets("Front")
Dim d
Dim j
d = 16
j = 1

Do Until IsEmpty(i.Range("HOSPITAL NAME" & j))

If i.Range("HOSPITAL NAME" & j) = hospital_Name Then
d = d + 1

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

End If
j = j + 1
Loop
End Sub
Reply
Clueless- Aug 10, 2014 03:39AM
Without trying to sound like an ignoramus, where do you type in this code?
Reply
New2This- Mar 16, 2016 05:36PM
In Visual Basic under the Developer Tab
Reply
Add comment
Helpful
+22
moins plus
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
Add comment
Helpful
+7
moins plus
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
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus February 12, 2016 Last seen - Jun 3, 2010 01:31PM
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
Reply
IanO- Jun 4, 2010 06:17AM
Would this copy the cell format (ie Borders) aswell as the text format (ie. Bold, Italics)
Reply
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus February 12, 2016 Last seen - Jun 4, 2010 06:50AM
it should copy all
Reply
IanO- Jun 4, 2010 10:02AM
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 ?
Reply
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus February 12, 2016 Last seen - Jun 4, 2010 10:38AM
Change this line

Rows(x).Paste to Rows(x).PasteSpecial
Reply
Add comment
Helpful
+4
moins plus
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.
Add comment
Helpful
+4
moins plus
Sorry, my apologies. This was already answered. Hallelujah! Thanks Rizvisa1!
Add comment
Helpful
+2
moins plus
This solution seems OK, but obviously I make some beginner mistakes. I wrote your code in VB editor, but I can't figure out how to start it, because I expect that automatically it will copy data from one sheet to another which is specified. I am asking you if you can help me hot to activate it, do I need to select some range, or add some command or anything else? I tried to find solution on websearch but unsuccessfull. Once again, I suppose this is some beginner mistake, but still I am unable to find it...

Anyway, thank you on your time!
Add comment
Helpful
+0
moins plus
just thought incase some one looks up this thread and has the same problem I would add this:

http://www.ozgrid.com/forum/showthread.php?t=50643
Add comment
Helpful
+0
moins plus
Please help me in getting the above macro to work on all the worksheets of a workbook. There are n number of worksheets with complex names.

Thanks,
Devendra
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus February 12, 2016 Last seen - Feb 16, 2010 08:01AM
Which above macro ? There is more than one routine in this thread.
Reply
Add comment
Helpful
+0
moins plus
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.
Add comment
Helpful
+0
moins plus
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
OQY- Oct 2, 2015 06:31AM
what does the word "TERM" represent
Reply
Hunter- Apr 19, 2016 11:00PM
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
Reply
Smith- Sep 17, 2016 01:23AM
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.
Reply
Add comment
Helpful
+0
moins plus
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
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!