+ Reply to Thread
Results 1 to 12 of 12

Searching Columns and Copying the respective Rows

  1. #1
    Registered User
    Join Date
    06-01-2011
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Unhappy Searching Columns and Copying the respective Rows

    I am trying to write a macro inside of Sheet 1 that will search Column J for certain terms one of them being "STEP" Once the search finds that term in Column J, I want it to copy the entire row to Sheet 2. The code I have researched and found doesnt seem to be doing it. Here is the code that I have been using:

    Please Login or Register  to view this content.
    Last edited by Erenagh; 06-02-2011 at 08:22 AM. Reason: didnt enter my code properly

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Searching Columns and Copying the respective Rows

    Hello Erenagh and welcome to the forum. Please take some time to read the forum rules and add code tags to your code. Look at Rule 3 located Here for details.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Searching Columns and Copying the respective Rows

    Does your code need to go through all the cells in column J?

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Searching Columns and Copying the respective Rows

    Erenagh,

    I revamped the code. Give this a try:
    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar

  5. #5
    Registered User
    Join Date
    06-01-2011
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Smile Re: Searching Columns and Copying the respective Rows

    Thanks TigerAvatar that fixed my problem! I was wondering also how to get it to print in different rows? What I mean is if I want it all to print starting on row 3 or row 45 how would I do that?
    Last edited by Erenagh; 06-02-2011 at 08:23 AM.

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Searching Columns and Copying the respective Rows

    Erenagh,

    You would change the For z = # to ... in the this line:
    Please Login or Register  to view this content.


    That # is the row that the macro starts on. Just change that to change the starting row.

    ~tigeravatar

  7. #7
    Registered User
    Join Date
    06-01-2011
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Searching Columns and Copying the respective Rows

    Thanks for the response! What I meant was where it printed on the other sheet like sheet 2. So if I wanted any information from sheet 1 to print on sheet 2 row 15 or 20. Depending on what I have going on.

    Quote Originally Posted by tigeravatar View Post
    Erenagh,

    You would change the For z = # to ... in the this line:
    Please Login or Register  to view this content.


    That # is the row that the macro starts on. Just change that to change the starting row.

    ~tigeravatar

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Searching Columns and Copying the respective Rows

    This part of the code (and same for ws3.Range...):
    Please Login or Register  to view this content.


    That code sets the destination row to the next empty line in the sheet. You could instead create a couple of variables to set the range to whatever you'd like. It would look like the following (destination is set to row 15 in the example, and proceeds from there)
    Please Login or Register  to view this content.


    Hope this helps,
    ~tigeravatar

  9. #9
    Registered User
    Join Date
    06-01-2011
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Searching Columns and Copying the respective Rows

    Everything has been working well but I have noticed when I manually filter inside Sheet 1 I get more results than after I run the macro, more exactely I am getting 10 when manually filtering and only 6 when running the macro. Also I changed some of the code to get all reports to show up on the same screen and it may be because of that. This is the code I was using

    Please Login or Register  to view this content.
    Last edited by Erenagh; 06-03-2011 at 09:46 AM.

  10. #10
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Searching Columns and Copying the respective Rows

    Erenagh,

    The code looks fine to me. I'd have to see a sample workbook experiencing the issue to find out what the problem is. Can you upload one?

    ~tigeravatar

  11. #11
    Registered User
    Join Date
    06-01-2011
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Searching Columns and Copying the respective Rows

    Here is the code that I am running. If you run the macro you can see that if you manually filter there are more results and some results aren't being placed where they should be.
    Attached Files Attached Files

  12. #12
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Searching Columns and Copying the respective Rows

    Erenagh,

    Your data table had cells with wrap text which was causing extraneous characters within the cells (like carriage returns or spaces). I changed the for next loop to the following and verified that it is now returning all the data correctly:
    Please Login or Register  to view this content.


    Hope that helps.
    ~tigeravatar

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1