Report

Copy rows based on a condition [Solved/Closed]

Ask a question Blossom83 - Last answered on Mar 3, 2017 at 06:31 AM by raghu
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
+97
plus moins
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
Clueless- Aug 10, 2014 at 03:39 AM
Without trying to sound like an ignoramus, where do you type in this code?
raghu- Mar 3, 2017 at 06:30 AM
I have typed this code , but nothing seems to getting copied , am i missing any step. Do i need to run this code , if yes how.
Reply
New2This- Mar 16, 2016 at 05:36 PM
In Visual Basic under the Developer Tab
sharatdotinfo 1Posts Tuesday December 13, 2016Registration date December 13, 2016 Last seen - Dec 13, 2016 at 02:12 PM
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
Reply
Anthony31- Jan 16, 2017 at 11:46 AM
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
Reply
Helpful
+22
plus moins
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
Helpful
+7
plus moins
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 January 6, 2016 Last seen - Jun 3, 2010 at 01:31 PM
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
IanO- Jun 4, 2010 at 06:17 AM
Would this copy the cell format (ie Borders) aswell as the text format (ie. Bold, Italics)
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - Jun 4, 2010 at 06:50 AM
it should copy all
IanO- Jun 4, 2010 at 10:02 AM
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 ?
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - Jun 4, 2010 at 10:38 AM
Change this line

Rows(x).Paste to Rows(x).PasteSpecial
Helpful
+4
plus moins
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.
Helpful
+4
plus moins
Sorry, my apologies. This was already answered. Hallelujah! Thanks Rizvisa1!
Helpful
+2
plus moins
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!
Helpful
+1
plus moins
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 at 06:31 AM
what does the word "TERM" represent
Hunter- Apr 19, 2016 at 11:00 PM
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 at 01:23 AM
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
Helpful
+0
plus moins
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
Helpful
+0
plus moins
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 January 6, 2016 Last seen - Feb 16, 2010 at 08:01 AM
Which above macro ? There is more than one routine in this thread.
Helpful
+0
plus moins
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.
Helpful
+0
plus moins
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
Harry- Nov 6, 2016 at 07:33 AM
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
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!