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

Copy rows from sheet 1 to sheet 2 if rule is met [Solved/Closed]

Report
Posts
2
Registration date
Tuesday March 1, 2016
Status
Member
Last seen
March 3, 2016
 -
Posts
2
Registration date
Tuesday March 1, 2016
Status
Member
Last seen
March 3, 2016
 -
Hi


I have a contact list I need to extract data from.
Full contact list is sheet 1
Names are in A2:A12948
Internal phone numbers in B2:B12948
DDI numbers in C2:C12948
Mobile numbers in D2:D12948

I would like to copy rows to sheet 2 if there is data in A and D
So users who only list a mobile number will be copied to sheet 2

Thank you

2 replies

Answer 1 / 2
Posts
2700
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 25, 2021
 457
Hi Sierranz,

The code below will do as requested. Results will be in column A & D of sheet2.

Here is the code: 
Sub RunMe()
Sheets("Sheet1").Select

For Each cell In Range("A2:A12948")
    If cell.Value <> vbNullString And _
    cell.Offset(0, 1).Value = vbNullString And _
    cell.Offset(0, 2).Value = vbNullString And _
    cell.Offset(0, 3).Value <> vbNullString Then _
    cell.EntireRow.Copy _
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell

End Sub
1
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

Answer 2 / 2
Posts
2
Registration date
Tuesday March 1, 2016
Status
Member
Last seen
March 3, 2016
Thank you TrowaD that worked great!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!