Copy line if

[Kcope8302]

I have a workbook with 4 Sheets(PTR,NMP,Reference data, Work Area). The first
2(PTR and NMP) are used to display the data from Work Area and Reference
Data.

In the Work Area I paste a dataset composed of all projects from the year
for all teams. In reference data I have a list of project numbers relevant
for my team.

For PTR and NMP to have the correct data I first need only the relevant
projects in the Reference tab to be seperated from all other projects in the
workarea sheet. Column A in both Reference Data and WorkArea have the project
numbers.

Then once I only have the projects relevant to my team I need columns C
and S to be searched for 'PTR', if found paste that whole line of data in the
PTR worksheet. Otherwise put it in the NMP worksheet.

What I am specifically looking for is a copy function that will first verify
that it is a project for my team and then that PTR is located in C or S. And
then another that verifies it does not have PTR in column C or S and pastes
those lines.

 

[Kcope8302]

Here is a better explaination and an example. There was a change in the
requirements that made it a bit easier:

I have a large dataset that I am importing from an outside source. This data
contains all project codes from a specified date. To be able to limit the
data I want to reference I want to compare that data against a list of
project codes specifically for my team and have those transferred to a
seperate worksheet where I will run different analysis on that data.

The raw data would be put in 'Work Area' worksheet. I have a 'Reference
Data' worksheet with the project codes I am working on. I would need column A
in Work Area to be compared to column A in Reference Data. If there is a
match I want that whole line copied to a worksheet call PTR.

Limited Example

Work Area
Prjt Code Site Owner Date
4858589 NY K. Copeland 5-12-08
4398348 PA A. Patel 6-08-08
3984845 PA K. Copeland 8-07-08
3039848 DC P. Baker 11-17-08
3043948 FL L. Johnson 3-12-09
3048485 GA T. Raines 5-14-09

Reference Data
Prjt Code
4858589
3984845
3043948

PTR
Prjt Code Site Owner Date
4858589 NY K. Copeland 5-12-08
3984845 PA K. Copeland 8-07-08
3043948 FL L. Johnson 3-12-09

 

[Kcope8302]

Here is a better explaination of exactly what I am looking to do:

I have a large dataset that I am importing from an outside source. This data
contains all project codes from a specified date. To be able to limit the
data I want to reference I want to compare that data against a list of
project codes specifically for my team and have those transferred to a
seperate worksheet where I will run different analysis on that data.

The raw data would be put in 'Work Area' worksheet. I have a 'Reference
Data' worksheet with the project codes I am working on. I would need column A
in Work Area to be compared to column A in Reference Data. If there is a
match I want that whole line copied to a worksheet call PTR.

Limited Example

Work Area
Prjt Code Site Owner Date
4858589 NY K. Copeland 5-12-08
4398348 PA A. Patel 6-08-08
3984845 PA K. Copeland 8-07-08
3039848 DC P. Baker 11-17-08
3043948 FL L. Johnson 3-12-09
3048485 GA T. Raines 5-14-09

Reference Data
Prjt Code
4858589
3984845
3043948

PTR
Prjt Code Site Owner Date
4858589 NY K. Copeland 5-12-08
3984845 PA K. Copeland 8-07-08
3043948 FL L. Johnson 3-12-09

 

[Kcope8302]

When I press CRT+A(shortcut key) and try to run this macro I get the
following error.

I get a Run-Time error '9':
Subscript out of range.

It then highlites the following row.
Set Rng = Sheets("Work Area").Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)

 

[Kcope8302]

It is telling me that it finds 0 matches. I made sure to put some in there
that do match the reference data worksheet. After I click the box to verify
there were 0 records found another box informing me of:

Runtime error '13"
Type mismatch

Thanks for your assistance,

 

[Kcope8302]

I have got it to where it now will recognize 1 record. Then it goes back to
the error previously stated. When VB is opened it highlites: i = ""

 

[Kcope8302]

The actual names of the Projects are CRDB#####. Could the fact that these are
not integer values be the reason for the error?

 

[Kcope8302]

I believe it is something with the full version that is not allowing it to go
thru. I will forward a full version.

Thanks,

 

[Kcope8302]

Thank you Simon, it worked perfectly!

Code:
--------------------
Sub Copy_Data()
Dim Rng As Range, Rng1 As Range, MyCell As Range, oCell As Range, i As Long
Set Rng = Sheets("Work Area").Range("A1:A" & Sheets("Work Area").Range("A" & Rows.Count).End(xlUp).Row)
Set Rng1 = Sheets("Reference Data").Range("A1:A" & Sheets("Reference Data").Range("A" & Rows.Count).End(xlUp).Row)
i = 0
For Each MyCell In Rng1
For Each oCell In Rng
If oCell.Value = MyCell.Value Then
oCell.EntireRow.Copy Destination:=Sheets("PTR").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
i = i + 1
End If
Next oCell
Next MyCell
Sheets("Reference Data").Range("N1:O" & Sheets("Reference Data").Range("O" & Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("PTR").Range("W2")
MsgBox "There were " & i & " items copied to PTR", vbInformation, "Record Count"
Sheets("PTR").Columns.AutoFit
i = 0
' Copy_Data Macro
'
' Keyboard Shortcut: Ctrl+a
'
End Sub
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Kcope8302]

One More issues I have run into.

When copying the columns from Reference Data it seems to be directly copying
the columns instead of verifying that lines A match-up between Reference data
and Work Area worksheets.

So at this point it is just copying columns N and O and pasting it into PTR.

Please help me if you can.

Thanks Again!

 

[Kcope8302]

Sorry for the confusion. I meant to state that the columns coming from
reference data should match the project codes being reference in column A.

Thanks,