Pulling an entire row "IF" a cell contains "x"

[anika.johnson]

Working with several sheets, how can I copy a entire rows from the "Main" sheet to the applicable sheet. The "if" would be determined from a column on the "Main" sheet.
Example: If column "D" contains 3 different codes, "x" "y" and "z"; and I want any of the rows that contain "x" in that column to be copied (in full, not just column "D") to Sheet "X", and so on.
Thanks for any help!!

 

[Lidsavr]

Here is a macro that I received from techonthenet.com that I modified for my use. I've changed the text to red to highlight the things you might need to change to make this work for you. For instance, the sheet names and the value (what you called X) in your message. Also, please not that I have several comments (lines that start with ' ) to help me identify what is going on with the sub set of data below the line.

'www.techonthenet.com/excel/macros/search_for_string.php
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 2
LSearchRow = 2
'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column D = "X", copy entire row to Sheet2
If Range("D" & CStr(LSearchRow)).Value = "X" Then
'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If


Also add the following to the bottom of your macro after the last "Exit Sub" and before the "End Sub":

Err_Execute:
MsgBox "An error occurred."

This will make a message box pop up if there is an error.

I hope this helps.

Charles

 

[ravishankar]

Hi
you can try these codes

Sub Anika()
Dim a As Long, x As Long, y As Long
x = Sheets("main").Cells(Rows.Count, 4).End(xlUp).Row
For a = 2 To x
Sheets("main").Rows(a).Copy
b = Sheets("main").Cells(a, 4)
y = Sheets(b).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(b).Range("A" & y + 1).PasteSpecial
Next a
End Sub

Ravi

 

[Peter_SSs]

Working with several sheets, how can I copy a entire rows from the "Main" sheet to the applicable sheet. The "if" would be determined from a column on the "Main" sheet.
Example: If column "D" contains 3 different codes, "x" "y" and "z"; and I want any of the rows that contain "x" in that column to be copied (in full, not just column "D") to Sheet "X", and so on.
Thanks for any help!!

Anika

Welcome to the MrExcel board!

Your question looks pretty similar to these two (related) threads.

http://www.mrexcel.com/forum/showthread.php?t=343121

http://www.mrexcel.com/forum/showthread.php?t=343325