Pulling data from one worksheet to another

[JoeJoe]

Hello everyone - Im not sure if what I am trying to do requires Macro
programming, but here's what I am trying to accomplish.

I have a workbook with two worksheets. Worksheet "A" includes a column
(column A) with various job titles - one job title within each cell.
"A" also has a column (column B) which reflects either a 1 or a 0. The
1 indicates the role has been filled, the 0 indicates the role is open.

The other worksheet ("B") is meant for the business unit lead to
provide verbiage as to the reason the open role(s) is needed. What I
would like to happen automatically is for the bworksheet B to pull only
the job titles that are indicated with a 0.

Lets assume Worksheet A includes 100 rows of data. Ten of those rows
reflect open roles (indicated with a 0). I would like for Worksheet B
to pull only the job titles that are indicated with the 0. Thus,
Worksheet B would summarize this info within 10 rows.

Hopefully this makes sense. Any suggestions would be greatly
appreciated.

 

[Simon Lloyd]

Try modifying this zip file i sent you, it has code and formula to
achieve what you want!

Regards,
Simon


+-------------------------------------------------------------------+
|Filename: Job Status.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5214 |
+-------------------------------------------------------------------+

 

[JoeJoe]

Try modifying this zip file i sent you, it has code and formula to
achieve what you want!

Regards,
Simon


+-------------------------------------------------------------------+
|Filename: Job Status.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5214 |
+-------------------------------------------------------------------+

Simon - this is very helpful. I am having one problem though. When I
run the Macro, it pastes the first name in the first empty cell within
the designated column. How can I adjust the macro so I can specify the
specific cell I want to start pasting at? Below is the macro I have
adjusted thus far:

Option Explicit

Sub jobs()
Dim mycell
Dim rng As Range
Dim rng1 As Range
On Error Resume Next
Set rng = Sheets("Dept_HC").Range("A1:A49")
For Each mycell In rng
If mycell.Offset(0, 8).Value = "0" Then
mycell.Copy
Sheets("Commentary").Select
With Sheets("Commentary")
Range("a2").Select
Set rng1 = Range("a:a").End(xlDown)(2)
rng1.Select
End With
ActiveSheet.Paste
End If
Next
End Sub

Thanks for your help

 

[Simon Lloyd]

The macro assumes you have data in the first row like a heading or titl
as i have done in my demo for you if you type in a heading or title i
A1 then it should work fine. You should remove the line On Error Resum
Next

Regards,
Simo