Selectively pulling data from one worksheet to fill in another

[jrob183]

Here is what I have. I have to two worksheets that will be constantly updated with contact lists. Then a third worksheet that has a contact list that is a subset of the first two worksheets. Each contact has multiple columns of data such as company name, position, phone number, etc. and each contact has its own row. I created a column where I can put a "1" and I want to make it so that excel searches that column in each of the first two worksheets for the "1" if it sees a "1" in that column it will pull everything in that row or everything in that row from lets say column C through K which ever is easier and put it into the third worksheet. In the end I want to have a contact list in the 3rd worksheet that contains all the contact information from contacts that I put a 1 next to in the first two worksheets.

Can anyone help?

 

[Alphacsulb]

How about copying and pasting them all into 1 sheet, and sorting by the column that has the "1".

This makes it less complicated.

 

[daverunt]

Hi,

here's a macro to try.
Give it a go on a copy.
It assumes the '1's will be in Column A. Worksheet 1 and 2 are the sheets to copy from.

There is a problem in that repeated running of the macro will copy the same data to sheet 3 next available row unless you remove the '1' from the sheets at the same time.

I have commented out the line that will do this.

Sub copydetails()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If Not WS.Name = "Sheet3" Then
 
WS.Activate
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each Cell In Rng
WS.Activate
If Cell.Value = "1" Then
'Cell.ClearContents
RowNum = Cell.Row
Range(Cells(RowNum, 3), Cells(RowNum, 11)).Copy
Sheets("Sheet3").Activate
Range("C1").Select
    Do While Not IsEmpty(ActiveCell)
    ActiveCell.Offset(1, 0).Select
    Loop
ActiveSheet.Paste
 
End If
Next
End If
Next
End Sub

 

[jrob183]

You sir are incredible. Thanks so much.

 

[klangham]

Maybe you can help me as well, since my problem is similar. I have a query from a database in one worksheet, and I want to input a number (1-112) into a cell in a new worksheet and have all the rows that have this number in a certain column pulled into the 2nd worksheet. I might not be explaining it well since I am not familiar with macros. I have several runs (1-112) that have information in a query-there are multiple rows for each run. I want to pull all the information for one run (several rows) into another worksheet when one of the runs is selected from a drop down. Thanks in advance to anyone who can help.