Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data that matches criteria in one worksheet to another worksh
Hi
I am needing to be able to search through a column of data in Worksheet 1 and copy the matched and corresponding data to another worksheet starting at row 4. The code that I have already used is: Sub cond_copy() Sheets("Sheet1").Select rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row For i = 1 To rowcount Range("a" & i).Select check_value = ActiveCell If check_value = "True" Or check_value = "true" Then ActiveCell.EntireRow.Copy Sheets("Sheet2").Select rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row Range("a" & rowcount + 1).Select ActiveSheet.Paste Sheets("Sheet1").Select End If Next End Sub This seems to work fine, apart from that it pastes the data and overwrites my heading in Row 2 of Worksheet 2. Where and what do I need to write to ensure that the copied data is placed in the correct position i.e. Row 4 on Sheet 2 Thanks in advanced for your help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data that matches criteria in one worksheet to another worksh
Try the below..
Sub cond_copy() Dim ws As Worksheet, lngRow As Long, lngNRow As Long lngNRow = 4 Set ws = Sheets("Sheet2") For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If UCase(Range("A" & lngRow).Text) = "TRUE" Then Rows(lngRow).Copy ws.Rows(lngNRow): lngNRow = lngNRow + 1 End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "angiec50" wrote: Hi I am needing to be able to search through a column of data in Worksheet 1 and copy the matched and corresponding data to another worksheet starting at row 4. The code that I have already used is: Sub cond_copy() Sheets("Sheet1").Select rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row For i = 1 To rowcount Range("a" & i).Select check_value = ActiveCell If check_value = "True" Or check_value = "true" Then ActiveCell.EntireRow.Copy Sheets("Sheet2").Select rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row Range("a" & rowcount + 1).Select ActiveSheet.Paste Sheets("Sheet1").Select End If Next End Sub This seems to work fine, apart from that it pastes the data and overwrites my heading in Row 2 of Worksheet 2. Where and what do I need to write to ensure that the copied data is placed in the correct position i.e. Row 4 on Sheet 2 Thanks in advanced for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from sheet to sheet when matches criteria | Excel Worksheet Functions | |||
copy if data matches | Excel Worksheet Functions | |||
Selecting data that matches certain criteria in one column | Excel Discussion (Misc queries) | |||
How can I get data from one worksheet into another exsiting worksh | Excel Discussion (Misc queries) | |||
How do I give all access to one worksheet but not to other worksh. | Excel Worksheet Functions |