# Solved: Excel Macro Search and Copy Rows

Discussion in 'Business Applications' started by computerman29642, Mar 12, 2009.

Not open for further replies.

Joined:
Dec 4, 2007
Messages:
2,895
I need to create a macro in Excel that will look for a certain text within a column (Column A), Copy the data in another column (Column B), and paste the data in another column (Column C).

For Example....

Column A
ABC1
ABC1
ABC1
ABC1
ABC1
DEF2
DEF2
DEF2

Column B
1000
1500
78
562
235
89
456
258

So, I would like to copy all the data in Column B that correspond with "ABC1" in column A, and paste that data in Column C.

Joined:
Dec 4, 2007
Messages:
2,895
I need to mention this is being done from one workbook to another.

Joined:
Dec 4, 2007
Messages:
2,895
This may help.....

Here is my original code

Code:
```For N = LBound(FileName1) To UBound(FileName1)
Set mybook = Workbooks.Open(FileName1(N))
Set sourceRange = mybook.Worksheets(1) '.Range("F500:F579")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "B")

rnum2 = 4
basebook.Worksheets(1).Cells(rnum2, "G").Value = mybook.Name

sourceRange.Copy destrange

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
```
Here is a code that I have been playing with...

Code:
```For sRow = 1 To Range("A65536").End(xlUp).Row
If Cells(sRow, "A") = "ABC1" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "F").Copy Cells(dRow, "C")
End If
Next sRow
```
How can I mix the two codes?

4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
When you say "from one workbook", do you mean that the active workbook is not the one housing the data?

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C1:C" & LastRow).FormulaR1C1 = "=IF(RC[-2]=""ABC1"",RC[-1],""X"")"
Range("C1:C" & LastRow).Value = Range("C1:C" & LastRow).Value
Columns(3).SpecialCells(xlCellTypeConstants, 2).Delete Shift:=xlUp

, for starters.

Joined:
Dec 4, 2007
Messages:
2,895
That would be correct. The Active Workbook is blank to begin with. The macro will actually prompt trhe user with an "Open" dialog box to select a file from a specifi directory.

6. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
So which wb actually houses the code?

Where did you get what you have so far? (assuming some of it's not yours) I'm thinking you might want to check out Ron's ADO examples.

Joined:
Dec 4, 2007
Messages:
2,895
I am setting the macro up so that it is available in all workbooks (Personal.xls). My original code hard coded the range to copy and paste. However, that is not the best option currently.

So, that is why I am trying to modify my original code.

The original macro has code that prompts the user to select an Excel file to open, and then copy and paste specific data from that file into the blank workbook.

8. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Gotcha.

While I've not worked with this kind of thing before, I'd imagine you could just assign the blank workbook/sheet names to variables, before they chose what to open:

x = ActiveWorkbook.Name
y = ActiveSheet.Name

And then use:

Range("A:B").Copy Workbooks(x).Sheets.Range("A1")
ActiveWorkbook.Close

, "ActiveWorkbook" there being the one they just opened. And then throw in the other code, as posted before.

Joined:
Dec 4, 2007
Messages:
2,895
Thanks Bomb. I will play around a little and see what i can come up with.

10. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
I know you will.

I guess if you store the active (i.e. blank) wb name before you run the Open dialog for them, then you need something like:

If ActiveWorkbook.Name = "whatever name you stored" Then Exit Sub

straight after to kill the whole thing; in case they backed out of the file opening. Or something.

TTYL

Joined:
Dec 4, 2007
Messages:
2,895
LOL...What can I say, I love learning this stuff and trying to figure things out.

Forgive my lack of knowledge, but what does "TTYL" mean?

12. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Well, I have nothing else to contribute , but I think that is Talk To You Later....

13. ### turbodante

Joined:
Dec 19, 2008
Messages:
744
I don't have anything to contribute either, then I don't understand the problem to start with. I would have suggested vlookup, but looks like some of the data cells are the same. I think I need to do an DSE assessment.

Joined:
Dec 4, 2007
Messages:
2,895
Man....I should have got that...LOL!

Joined:
Dec 4, 2007
Messages:
2,895
I appreciate the thought.

I believe Bomb has set me in the right direction. I have more testing to do before I can be completelt sure though.

As Seen On