Copy line if

KCope, can you possibly provide a sample workbook?
*How to get further help with a workbook*
For further help with it why not join our forums (shown in
the link below) it's completely free, if you do join you will have the
opportunity to add attachments to your posts so you can add workbooks to
better illustrate your problems and get help directly with them. Also if
you do join please post in this thread (link found below) so that people
who have been following or helping with this query can continue to do
so.

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

"Kcope8302" wrote:

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" wrote:

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,

"Simon Lloyd" wrote:


Thats because Excel thinks the sheet doesn't exist, it may be
that there
is an extra space in the name in the code or your worksheet tab
or it's
mis spelled.

Kcope8302;423534 Wrote:
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)



"Simon Lloyd" wrote:


This should do what you need:

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" & Range("A" &
Rows.Count).End(xlUp).Row)
Set Rng1 = Sheets("Reference Data").Range("A1:A" & 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
MsgBox "There were " & i & " items copied to PTR",
vbInformation,
"Record Count"
i = ""
End Sub
--------------------





--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile:
Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: ' Copy line if - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117763)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

Attached is a file with a limited data extract. It should be sufficient
for the purposes of adjusting the macro as needed. The current issues I
am having are that after I click the box to verify there were 0 records
found another box informing me of:

Runtime error '13"
Type mismatch


Again I am looking for:

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


+-------------------------------------------------------------------+
|Filename: PTR Limited.xlsx |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=182|
+-------------------------------------------------------------------+

--
kcope8302
------------------------------------------------------------------------
kcope8302's Profile: http://www.thecodecage.com/forumz/member.php?userid=513
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

All sorted, it found and copied 88 matches as you can see in the
attached.

kcope8302;423953 Wrote:
Attached is a file with a limited data extract. It should be sufficient
for the purposes of adjusting the macro as needed. The current issues I
am having are that after I click the box to verify there were 0 records
found another box informing me of:

Runtime error '13"
Type mismatch


Again I am looking for:

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


+-------------------------------------------------------------------+
|Filename: PTR Limited.xlsm |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=183|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

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

Thanks,

"Simon Lloyd" wrote:


All sorted, it found and copied 88 matches as you can see in the
attached.

kcope8302;423953 Wrote:
Attached is a file with a limited data extract. It should be sufficient
for the purposes of adjusting the macro as needed. The current issues I
am having are that after I click the box to verify there were 0 records
found another box informing me of:

Runtime error '13"
Type mismatch


Again I am looking for:

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


+-------------------------------------------------------------------+
|Filename: PTR Limited.xlsm |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=183|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

I think it was just the fix that i did in the code, if you want to send
a full version and it has sensitive data you can PM me and i will give
you my email address.

Kcope8302;424025 Wrote:
I believe it is something with the full version that is not allowing it
to go
thru. I will forward a full version.

Thanks,

"Simon Lloyd" wrote:


All sorted, it found and copied 88 matches as you can see in the
attached.

kcope8302;423953 Wrote:
Attached is a file with a limited data extract. It should be
sufficient
for the purposes of adjusting the macro as needed. The current
issues I
am having are that after I click the box to verify there were 0
records
found another box informing me of:

Runtime error '13"
Type mismatch


Again I am looking for:

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


+-------------------------------------------------------------------+
|Filename: PTR Limited.xlsm |
|Download:
http://www.thecodecage.com/forumz/attachment.php?attachmentid=183|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: ' Copy line if - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117763)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

It would be easier to send it thru email. It is not allowing me to
upload.

Thanks


Kcope8302;423534 Wrote:
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)



"Simon Lloyd" wrote:


This should do what you need:

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" & Range("A" &
Rows.Count).End(xlUp).Row)
Set Rng1 = Sheets("Reference Data").Range("A1:A" & 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
MsgBox "There were " & i & " items copied to PTR", vbInformation,
"Record Count"
i = ""
End Sub
--------------------



Kcope8302;423370 Wrote:
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


"Simon Lloyd" wrote:


That sounds a little confusing, is it possible you could upload
a
sample
workbook?
For further help with it why not join our forums (shown in
the link below) it's completely free, if you do join you will
have
the
opportunity to add attachmnets to your posts so you can add
workbooks
to
better illustrate your problems and get help directly with them.
Also
if
you do join please post in this thread (link found below) so
that
people
who have been following or helping with this query can continue
to do
so.
*Why not add a workbook?*
Providing a workbook will not only get you your answer quicker
but
will
better illustrate your problem, usually when we can see your
data
(-it
can be dummy data but must be of the same type-) and your
structure
it
is far easier for us to give you a tailored, workable answer to
your
query

Kcope8302;416232 Wrote:
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.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help -
Microsoft
Office Discussion' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com)))


------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile:
Simon
Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd'
(http://www.thecodecage.com/forumz/member.php?userid=1))
View this thread: 'Copy line if - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=115857)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=115857




--
kcope8302
------------------------------------------------------------------------
kcope8302's Profile: http://www.thecodecage.com/forumz/member.php?userid=513
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

kcope8302;424035 Wrote:
It would be easier to send it thru email. It is not allowing me to
upload.

ThanksThis code works perfect!

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

Thank you Simon, it worked perfectly!

"Simon Lloyd" wrote:


kcope8302;424035 Wrote:
It would be easier to send it thru email. It is not allowing me to
upload.

ThanksThis code works perfect!

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

Glad we could be of help!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763

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!

"Simon Lloyd" wrote:


kcope8302;424035 Wrote:
It would be easier to send it thru email. It is not allowing me to
upload.

ThanksThis code works perfect!

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763