+ Reply to Thread
Results 1 to 4 of 4

Split worksheet with many rows into separate files based on changes in one column

  1. #1
    Registered User
    Join Date
    03-20-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Split worksheet with many rows into separate files based on changes in one column

    Hello.
    I have a problem I'm trying to solve and am hoping someone on here might be able to help.

    I have a spreadsheet with about 55 columns and 3,000 rows. Each row belongs to one of 103 different individuals.
    Each of the records contains the person's name and their email address.

    I want to send 103 separate emails (via Outlook) where each email includes an attachment of that individual's records.
    The body of the email will be the same for each person. The only thing that will be different for each one is the attachment.

    If someone can help me create separate files based on the spreadsheet, I don't mind doing the emails manually and attaching.

    However, if someone can help me figure out how to automate the whole process, that would be great.

    Any help or insight is appreciated.
    Thanks for reading.

    Regards,
    Thom

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Split worksheet with many rows into separate files based on changes in one column

    Perhaps this is a possibility

    Using this sample data...
    Data Range
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    6
    Name
    Email
    Jan
    Feb
    Mar
    Apr
    May
    Jun
    Jul
    Aug
    Sep
    Oct
    Nov
    Dec
    7
    Alpha
    Alpha@Company.com
    995
    764
    238
    737
    611
    118
    352
    257
    934
    350
    244
    196
    8
    Alpha
    Alpha@Company.com
    259
    676
    765
    628
    397
    317
    190
    541
    761
    971
    531
    391
    9
    Alpha
    Alpha@Company.com
    630
    129
    542
    109
    518
    164
    459
    588
    821
    989
    755
    373
    10
    Alpha
    Alpha@Company.com
    996
    413
    750
    246
    828
    885
    860
    320
    332
    334
    404
    426
    11
    Bravo
    Bravo@Company.com
    477
    258
    199
    724
    976
    651
    165
    803
    226
    740
    208
    163
    12
    Charlie
    Charlie@Company.com
    538
    416
    833
    969
    205
    354
    371
    661
    754
    739
    322
    380
    13
    Delta
    Delta@Company.com
    925
    474
    925
    680
    773
    160
    760
    712
    257
    684
    125
    162

    I created a pivot table based on that data, using only two fields: Name (as a Row label) and Count of Email (as a Value field)
    That pivot table looks like this:
    Data Range
    C
    D
    5
    Row Labels
    Count of Email
    6
    Alpha
    4
    7
    Bravo
    1
    8
    Charlie
    1
    9
    Delta
    1
    10
    Grand Total
    7

    Now, when you double click on an email count...Excel creates a new sheet that only contains the detail rows for that person.

    The last step would be to move or copy the detail tabs to new workbooks.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    03-20-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Split worksheet with many rows into separate files based on changes in one column

    I've attached a sample file. Based on this, I want to create 3 separate emails, each with the same body, but each has a different file attachment.
    Hope that makes sense.

  4. #4
    Registered User
    Join Date
    03-20-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Split worksheet with many rows into separate files based on changes in one column

    Thanks Ron. Unfortunately, I'm looking to have the files fully generated and attached to an email and sent out. Manually creating the files and sending the emails will take me most of the day. I'm looking to automate it so I can use the same method in the future.

+ 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. I would like to split one worksheet into many files based on criteria in two columns
    By paulfields4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-26-2015, 07:34 PM
  2. split/separate rows based on criteria and send those rows by mail
    By katu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-09-2014, 08:59 AM
  3. Split the excel data in worksheet to save it in two separate excel files
    By sat1591 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-20-2014, 04:52 AM
  4. VBA code to split a worksheet into separate excel files.
    By sambo24 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2013, 06:15 AM
  5. [SOLVED] How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column
    By UzieJacuzzi in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 07-31-2012, 07:26 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