Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Summarising Registrations

    Hi All I'm after some assistance once again.

    I receive numerous registrations via email for attendance to various conferences.

    It is a very time consuming exercise to cut and paste the details of attendees into a summary sheet - I've tied an Offset option but this does not look to be a practical option.

    In the attached spreadsheet, in sheet 1 is where I have pasted details directly from the registration emails (there may be any number of these depending on the conference up to a 100 or so) into columns A,C,E,G, etc and in sheet 2 is the summary sheet from the data in sheet 1 under columns names.

    Sheet 3 is a what the registration form looks like when pasting as a picture

    Does anyone have any suggestions on how this summarisation process could be automated (maybe some code?)

    Any thoughts/suggestions/solutions would be greatly appreciated

    Regards
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Ok, here's the basic setup.

    Code:
    Option Explicit
    
    Sub CreateSummary()
    
       Dim lColCntr As Long
       Dim lRowCntr As Long
       Dim wksSrc   As Worksheet
       Dim wksDest  As Worksheet
       
       
    '***  Setup ***
    
       Application.ScreenUpdating = False   'Keep screen from blinking
       Set wksSrc = ActiveWorkbook.Worksheets("Detail")
       Set wksDest = ActiveWorkbook.Worksheets("Summary")
       wksSrc.Activate
       
       lRowCntr = 2   'First summary row is 2
       lColCntr = 2   'First data is in B
       
    '*** Process ***
    
       Do
       
         With wksDest
             .Cells(lRowCntr, 1).Value = Cells(2, lColCntr).Value  'Date/Venue
             .Cells(lRowCntr, 2).Value = Cells(4, lColCntr).Value  'First Name
             .Cells(lRowCntr, 3).Value = Cells(6, lColCntr).Value  'Last Name
             
         End With  'wksDest
         
         lColCntr = lColCntr + 2  'Move to next data column
         lRowCntr = lRowCntr + 1  'Move to next row in Summary
         
       Loop Until Cells(2, lColCntr).Value = ""
    
    
    End Sub  'Create Summary()
    Notes:

    I renamed the sheets Detail & Summary the code as is will not work if you don't do that, although you could use different names as long as you change them in the code.

    I didn't do all the fields as I think you'll get the gist.

    Remember when you get to the address field it takes up 3 rows so you'll have to concatenate the three items into a single cell.

    Remember to save the file as .xlsm or .xlsb after you add the code.

    HTH
    Last edited by RetiredGeek; 2015-10-25 at 22:33.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    verada (2015-10-26)

  4. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks RetiredGeek - that is just what I need.

    Will save hours of work

    Very much appreciated.

    Regards

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Verada,

    If you are using outlook, you can create a rule to send the emails to a specific folder. From there, Excel vb code can pull the information from the email directly into the spreadsheet then use RG's fine code to manipulate it.

    Maud

  6. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud - what a great idea.

    Do you have an example of the Excel vb code that I would need to pull the info into the spreadsheet?

    Cheers

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Better yet, do you have a sample outlook email you can zip and post? To create a rule, is the subject of the emails always the same? What if the same person registers to the venue twice?

    See if this is something like you are looking to do.
    http://windowssecrets.com/forums/sho...Email-to-Excel
    Last edited by Maudibe; 2015-11-04 at 22:09.

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    verada (2015-11-04)

  9. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Maud - I thinks I can do something with the code in the link.

    Your assistance is greatly appreciated.

    Regards

Posting Permissions

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