+ Reply to Thread
Results 1 to 15 of 15

Macro to copy data from all columns containing a specified header to a combined column

  1. #1
    Registered User
    Join Date
    04-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    58

    Red face Macro to copy data from all columns containing a specified header to a combined column

    Hi - I am a macro newbie and I think this is beyond me. :/

    I've been trying all day with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.

    So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database. Or even if they were just pasted into one column within the same worksheet so I could copy paste.

    There must be a way to do this that is easier than searching the internet all day again tomorrow lol. Can anyone help?

    Thanks for you time.

    ~Mamagregory

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Macro to copy data from all columns containing a specified header to a combined column

    I suggest posting a sample workbook with sample data with multiple tabs indicating the possibilities, then a proposed layout or solution. Then ask if it is possible or how to go about doing so.

    Describing your data set and solution with words is not typically ideal.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    04-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    58

    Question Re: Macro to copy data from all columns containing a specified header to a combined column

    Its a fairly simple thing I would hope. Not sure how to post a sample. Basically it is this

    Column1 Column 2
    Customer Name
    Customer Number

    Voucher 1 Voucher 2
    HB215454465 HB542455633
    HB546574366 HB098878895
    HB456477731 Etc.

    Other tabs with the same but different numbers.

    I need all the coupon numbers (HB2154465 etc) with Voucher 1 listed above them combined into a single column.

    How would I add a sample file?

    Thanks

    ~Mamagregory

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Macro to copy data from all columns containing a specified header to a combined column

    I'm on my phone, but search how to attach in a message.

  5. #5
    Registered User
    Join Date
    04-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Macro to copy data from all columns containing a specified header to a combined column

    I think I got the attachment to work - there should be a file called Test Coupon Macro Data attached. I am hoping it is a simple problem. Let me know if you have any ideas.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Macro to copy data from all columns containing a specified header to a combined column

    So lets start over, you want all the couchers from ALL the tabs in THIS workbook right, and not OTHER workbooks?

    You want to aggregate all the vouchers from all the tabs (will there be only 3?) to the Voucher Database tab?
    Will row 3 always include the Voucher # where # represents the number?
    Will, there only be 3 vouchers and thus only 3 columns needed in the database?

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

    Re: Macro to copy data from all columns containing a specified header to a combined column

    Maybe:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Macro to copy data from all columns containing a specified header to a combined column

    "So lets start over, you want all the couchers from ALL the tabs in THIS workbook right, and not OTHER workbooks?"

    Correct.


    "You want to aggregate all the vouchers from all the tabs (will there be only 3?) to the Voucher Database tab?"

    Yes I want to aggregate the vouchers from all the tabs to the database tab but in the actual data there will be a lot more than 3 - it depends how many people turn in coupons but last year we had 88 I think. There could be as many as 20 columns in each tab also. That's why I need a macro for this and can't just do it by hand.

    "Will row 3 always include the Voucher # where # represents the number?"

    Yes.

    "Will, there only be 3 vouchers and thus only 3 columns needed in the database?"

    No - the vouchers represent dates. So August 1 to 14 might be Voucher 1, August 14 - Sept 2, Voucher 2, Sept 3 to Sept 12 Voucher 3 etc. Last year we had about 8 but I'd imagine we could get up to 12 or so.

    If possible I don't need formatting in the results (color shading etc.) but that is not a big deal to remove either.

  9. #9
    Registered User
    Join Date
    04-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Macro to copy data from all columns containing a specified header to a combined column

    Re: John H

    Thanks for your help - it ran beautifully - only a couple potential issues. 1. the dates were in there. I tried putting the column with the dates above the one that says Voucher 1 etc. and rerunning but that didn't do anything. Is there a way to change it to not get the dates (easiest might be like I said to put the date column above the voucher 1 column).

    and 2. The actual sheet names won't be "Farm 1, Farm 2" etc. - they will be the names of the farms. i.e. MacDonald's Dairy etc. I had to remove the names for confidentiality. If needed I could create a duplicate of each sheet named Farm and then a number, but is there an easier way to do it? Can we just set it to look at all sheets since the first sheet is the only one to ignore and that won't have the word voucher in it anywhere?

    and 3. I don't need the formatting - is there an easy way to paste without formatting?

    Will this work if there are more columns as explained above??

    Thanks again!

    ~Mamagregory
    Last edited by Mamagregory; 04-15-2014 at 09:23 AM.

  10. #10
    Registered User
    Join Date
    04-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Macro to copy data from all columns containing a specified header to a combined column

    Another thought - each time I run it I only really need the most recent voucher - maybe there is a way of setting it so I just change part of the macro to discover the coupons that correspond with the most recent voucher # and add it to the next blank column in the vouchering database?

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

    Re: Macro to copy data from all columns containing a specified header to a combined column

    Try:

    Please Login or Register  to view this content.
    If there are move voucher numbers it will have to be revised.

  12. #12
    Registered User
    Join Date
    04-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Macro to copy data from all columns containing a specified header to a combined column

    I made a reply but it disappeared!

    To add more Vouchers I would just copy this part:

    Case Is = "VOUCHER 3"
    Range(Cells(5, rcell.Column), Cells(ActiveSheet.UsedRange.Rows.count + 1, rcell.Column)).Copy
    Sheets("Vouchering Database").Range("E" & Rows.count).End(3)(2).PasteSpecial xlPasteValues

    And replace VOUCHER 3 with VOUCHER 4 and Range("E" with G?

    Will this work with longer coupon amounts (i.e. it counts to the bottom of the coupons not a certain number, right?)?

    What if the Tabs do not say "farm 1" but rather the actual name of the farm? Is there a way to set it to just check all sheets since the first sheet won't say voucher and the other sheets will just be named Smith's Farm, Pete's Dairy etc.

    Thanks again for all your help with this - I have to scan in the coupons and they're increasing the amount by several hundred thousand again this year (but not my pay ) so this will make my job a little easier. ~

  13. #13
    Registered User
    Join Date
    04-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Macro to copy data from all columns containing a specified header to a combined column

    Or can I at least make i a four digit number? I tried changing it to For i = 0000 To 9999 but that didn't work. Most of the farms are assigned a four-digit number and I could use this instead of the name of the farm on the tabs. Would make some of the other stuff I do harder because I wouldn't be able to search alphabetically though so ideally a way to just search the whole worksheet would be best.

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

    Re: Macro to copy data from all columns containing a specified header to a combined column

    Question 1

    "To add more Vouchers I would just copy this part:

    Case Is = "VOUCHER 3"
    Range(Cells(5, rcell.Column), Cells(ActiveSheet.UsedRange.Rows.count + 1, rcell.Column)).Copy
    Sheets("Vouchering Database").Range("E" & Rows.count).End(3)(2).PasteSpecial xlPasteValues

    And replace VOUCHER 3 with VOUCHER 4 and Range("E" with G?"

    Yes

    Question 2

    "Will this work with longer coupon amounts (i.e. it counts to the bottom of the coupons not a certain number, right?)?"

    Yes

    Question 3

    "What if the Tabs do not say "farm 1" but rather the actual name of the farm? Is there a way to set it to just check all sheets since the first sheet won't say voucher and the other sheets will just be named Smith's Farm, Pete's Dairy etc."

    Will post an alternative shortly.

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

    Re: Macro to copy data from all columns containing a specified header to a combined column

    Modified for all sheets.

    Please Login or Register  to view this content.
    Red font indicates sheets you do not want to copy from.

+ 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. [SOLVED] Macro to copy data from a row (consist of multiple columns) into to single column
    By kammariarun in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-06-2013, 06:25 PM
  2. Replies: 1
    Last Post: 10-05-2013, 03:35 PM
  3. Copy Data from Several Columns with Same Header into One Column
    By tgilbride in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-21-2013, 12:38 PM
  4. Replies: 2
    Last Post: 12-24-2012, 12:46 PM
  5. Macro to rearrange/add columns based on column header?
    By drdavidge in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2006, 09:35 PM

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