Linking worksheets, but exceptions

  • Thread starter Thread starter Emily
  • Start date Start date
E

Emily

I have a worksheet with columns A - W. Column P contains names of a
contractor. I want 7 more worksheets named the contractor names ( I can do
that), but I only want their information on that chart. Also, I only want
the information if Column K=Active, Column J does not have "N/A=do not
audit", or if Column V=no. Please help.

Thanks in advance,
 
You can use autofilter to select a contractor's name and then apply
the conditions for columns K, J and V (using Custom...). Alternatively
for this last part, you can combine the conditions in one formula in a
helper column and then filter the helper column in one operation.

Then you can highlight the visible data and copy/paste into the
appropriate sheet for that contractor.

Hope this helps.

Pete
 
Emily
This little macro will do that for you. I assumed that your main sheet
was named "Main". This macro will copy the row and put it in the proper
contractors sheet if Column J IS NOT EQUAL TO "N/A=do not audit" and Column
V IS NOT EQUAL TO "No" and Column K IS EQUAL TO "Active". Post back if you
need more. HTH Otto
Sub ParseData()
Dim rColA As Range
Dim i As Range
Sheets("Main").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
If Cells(i.Row, 11) = "Active" And _
UCase(Cells(i.Row, 10)) <> "N/A=DO NOT AUDIT" And _
UCase(Cells(i.Row, 22)) <> "NO" Then
With Sheets(Cells(i.Row, 16).Value)
i.Resize(, 23).Copy
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
xlPasteValues
End With
End If
Next i
End Sub
 
Thanks for the help. I am not sure how to run macros, but a friend is going
to try and help me. I may email back for some macros help later. Thank you
for the help.
 
Emily
If you need help in placing and running the macro, email me and I'll
send you the little file I used to develop the macro. My email address is
(e-mail address removed). Remove the "extra" from this email. Otto
 
Back
Top