Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 2 of 2 Posts

·
Registered
Joined
·
2 Posts
Discussion Starter · #1 ·
I'll try to be as descriptive as possible.

Current Status:
I have an excel file with two sheets: sheet1 and sheet2.
Column "A" will have lots of data.
Some rows in column "A" (or the first cell for that row) will have the word "Directory".

Intent:
I need to search sheet2, only in column "A", for the row that contains the word "Directory" and copy that whole row onto sheet1 in the same row. Continue doing that until it completes through all of the rows.

Example Data:

(Before)
sheet2
Row Column A Column B Column C
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 Directory cool text huh?????
5 blah blah blah blah blah blah

sheet1
Row Column A
Row Column A Column B Column C
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 blah blah blah blah blah blah
5 blah blah blah blah blah blah

*Action: Column "A" find "Directory" Copy Row 4 to sheet1

(After)
sheet1
Row Column A
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 Directory cool text huh?????
5 blah blah blah blah blah blah
 

·
Registered
Joined
·
8,546 Posts
"copy that whole row onto sheet1 in the same row"

Do you mean like this?

(make sure what you already have is "safe" before you try, obviously)

Sub test()
If ActiveSheet.Name <> "Sheet2" Then Exit Sub
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("A1:A" & LastRow)
If InStr("Directory", Cell.Value) <> 0 Then
x = Cell.Row
Rows(x).Copy
Sheets("Sheet1").Cells(x, 1).Insert Shift:=xlDown
End If
Next Cell
Application.CutCopyMode = False
End Sub
 
1 - 2 of 2 Posts
Status
Not open for further replies.
Top