If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy line if
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. |
#2
|
|||
|
|||
Copy line if
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' (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=115857 |
#3
|
|||
|
|||
Copy line if
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' (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=115857 |
#4
|
|||
|
|||
Copy line if
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 "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' (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=115857 |
#5
|
|||
|
|||
Copy line if
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' (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=115857) -- 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=115857 |
#6
|
|||
|
|||
Copy line if
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' (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=115857) -- 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=115857 |
#7
|
|||
|
|||
Copy line if
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) ------------------------------------------------------------------------ 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 |
#8
|
|||
|
|||
Copy line if
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) ------------------------------------------------------------------------ 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 |
#9
|
|||
|
|||
Copy line if
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) ------------------------------------------------------------------------ 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 |
#10
|
|||
|
|||
Copy line if
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) ------------------------------------------------------------------------ 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 |
Thread Tools | |
Display Modes | |
|
|