+ Reply to Thread
Results 1 to 12 of 12

MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    10-17-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    Can someone please point me in the right direction, I am totally lost.....

    I need a MACRO that will search for the letter "x" in a certain column, and if found then copy and paste the row of information associated into a MASTER sheet. The tabs are not named the same but are identical in layout. Search would start at the 8th row of each sheet. This needs to be done for multiple worksheets in the same workbook. I have attached a workbook of what I need.

    Note the U004 & U005 are the sheets that I need information from. Going into the master sheet. Wherever there is an "X" in column X, I need that row pasted into Master.

    Thanks for the help in advance
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    Try the attached.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    Maybe:

    Sub cwitty22()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "MASTER" Then
        ws.Range("X7:X" & ws.UsedRange.Rows.count + 1).AutoFilter 1, "X"
        ws.Range("A8:X" & ws.UsedRange.Rows.count + 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Master").Range("A" & Rows.count).End(3)(2)
        End If
        ws.AutoFilterMode = False
    Next ws
    End Sub

  4. #4
    Registered User
    Join Date
    10-17-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    Wow that was fast thanks AB33!!!

    Nice work, really I appreciate it.

    Couple more questions for ya.

    Lets say I would want to just copy information from a couple of the columns....Is that hard to do?

    Also, instead of just reading an 'X', how would I go about reading 'XX' also? Is that too many of the same characters?

    Thanks again

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    Mind you if you change X to XX, if there is no exact match, we have to change the code from whole match to part match, but it will copy the same rows anyway.

  6. #6
    Registered User
    Join Date
    10-17-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    If columns cant be done no big deal the bulk of the work is already done with your MACRO.. Once again, Thanks. You are a life saver.

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    Chang this line of the code (At the top) of the code

    sFind = "x"
    INTO

    sFind = "xx"
    Yes, you can copy certain columns. Which columns?

  8. #8
    Registered User
    Join Date
    10-17-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    AB33 there will be an exact match.. Some of these components are marked XX for reference but not all sheets have this.

    So if i just put the code sFind = "XX" under the code sFind = X thats all I need to change?

    And for the columns, unfortunately we use a program that spits these BOMs out into this certain format so there is information spread across a couple columns (as you can see).

    I would need:
    Columns D-E (supplier detail no)
    Columns J-L (name)
    Column M (material)
    Columns N-P (specs)
    Columns Q - T are fine the way they are
    and if possible it would be good to show column X.

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    You have lots of merged cells and wrap texts. I do not know which one is column C or D. I assumed all non-contagious columns will be copied next to each other
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-17-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    Works great! Thanks

  11. #11
    Registered User
    Join Date
    10-17-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    One last thing AB33,

    In this new file that you send everything works great except when I try to add different sheets and run it again, it replaces the existing data.. Any way of having the macro just pick up where the first open spot is?? I do not want it to clear as I have multiple files that I need to add to this one...

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: MACRO for searching column for a letter and Copy/Paste the row into new MASTER sheet

    If you want to copy in to next empty row, remove this line

    ms.Range("A2:X" & Rows.Count).ClearContents

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 11
    Last Post: 05-12-2013, 11:30 AM
  2. [SOLVED] Macro Copy, Paste Transpose to Master Sheet
    By dingo7448 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2013, 04:15 PM
  3. Help with Copy and Paste Macro from Master sheet to other sheets
    By Livetorace27 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-11-2013, 03:19 PM
  4. [SOLVED] Macro to copy paste Tables to Master sheet
    By dawnmau in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-02-2013, 06:48 PM
  5. Select all sheet copy paste as value & except Master 1 & Master 2
    By tek9step in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-29-2010, 06:49 AM

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