1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro Search and Copy Rows

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

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    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.
     
  2. computerman29642

    computerman29642 Thread Starter

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

    computerman29642 Thread Starter

    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

    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.
     
  5. computerman29642

    computerman29642 Thread Starter

    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

    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.
     
  7. computerman29642

    computerman29642 Thread Starter

    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

    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(y).Range("A1")
    ActiveWorkbook.Close


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

    computerman29642 Thread Starter

    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

    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. :D

    TTYL :)
     
  11. computerman29642

    computerman29642 Thread Starter

    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

    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

    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.
     
  14. computerman29642

    computerman29642 Thread Starter

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

    computerman29642 Thread Starter

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

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

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/808743

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice