+ Reply to Thread
Results 1 to 5 of 5

Need dyanmic Userform to split all worksheet based on specific headers and mail

  1. #1
    Forum Contributor
    Join Date
    07-27-2018
    Location
    Jodhpur, India
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 Pro 64-bit)
    Posts
    405

    Need dyanmic Userform to split all worksheet based on specific headers and mail

    Dear Expert,

    I'm a MIS person, however I'm new to vba coding and am learning as I go. I need your assistance to create a userform that will allow me to split the workbook, including all worksheets, depending on particular column header criteria and save the entire workbook to the desired or selected folder. Now the most important part is that I want to make it dynamic as a universal programme for all workbooks, so that if I need to split a workbook including all worksheets depending on any certain column header's criteria that is included in the all worksheet's data, I can do so without having to make any more modifications.

    While splitting the workbook, I need to follow particular criteria, such as:
    If any worksheet has grouping, it will be the same in the splitted workbook's worksheet.
    If a worksheet does not have a gridline, the worksheet in the splitted workbook will be the same.
    If any worksheet zoom is customised, the zoom in that splited workbook's worksheet will be the same.
    If any worksheet cells have a merge filed, the splittted workbook's worksheet will have the same merge filed.

    Now, the advanced requirement is that I want to use the buttons below in the userform:
    Select File: Choose a file that needs to be split.
    Select Header: Based on the worksheet selected, all headers will appear in a drop down list from which we can select the column header for splitting the file, which includes all worksheets. One challenge that may be encountered is that if the column header starts with 3 or 4 or any row, we can also select the row no. for fetching the column header for splitting the file.
    Report Folder: Choose a Report Folder to save new splitted files.
    Split File: Split the workbook, including all worksheets, using the Split File-Command button.
    Selection Snap Range: Uses a customised selection range to deliver a snap via Outlook mail body. (When you select this option, the selection dialogue range will automatically open.)
    Email List: To add receiver's email id through a sub page of userform where we can add the mail id (if we want to split the workbook's all worksheets based on a column header e.g. Person/Dealer name, the first column of the email list will be the column header item name that can be a person or a dealer, then the remaining format will be as a tis To, CC, BCC, SUBJECT, In addition, all emails must include an HTML signature. We can get an email list template (template button) and fill in the header information before importing it into the userform page to display.
    Send Mail: To send all split files from the folders where fresh files will be split (each time only the latest file will be automatically attached in the outlook mail and will be sent)

    Close: Closes the userform


    I've attached a sample file with some worksheets and certain column headers that are the same on each worksheet. For example, if I want to split this workbook, including all worksheet data, into a new workbook based on column header "MD Name" (assuming I've selected Summary worksheet), the workbook will be automatically split, including all worksheet data, and a new workbook will be created and saved in the desired or selected folder.


    I understand that this is a fairly dynamic requirement, and that an expert might refuse to create for this thread. However, this feature is not available in any Excel add-in or anyplace else, and it is critical in the working part of sending achievement trackers, where a lot of information is provided in multiple worksheets of a workbook, and we must split and distribute the tracker for a certain person or dealer. Manually filtering a column or copying and pasting and creating files is a laborious and time-consuming task. That's why I'm trying to make it more dynamic.

  2. #2
    Forum Contributor
    Join Date
    07-27-2018
    Location
    Jodhpur, India
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 Pro 64-bit)
    Posts
    405

    Re: Need dyanmic Userform to split all worksheet based on specific headers and mail

    Dear expert, Please help me on this post.

  3. #3
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Need dyanmic Userform to split all worksheet based on specific headers and mail

    this is somewhat more than a question/answer scenario - you are specifying an application, therefore I suggest you should be prepared to pay and should use the 'commercial services' offered on this site.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  4. #4
    Forum Contributor
    Join Date
    07-27-2018
    Location
    Jodhpur, India
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 Pro 64-bit)
    Posts
    405

    Re: Need dyanmic Userform to split all worksheet based on specific headers and mail

    I do not know much about it, I looked at the commercial services webpage but couldn't figure out how to use it.

  5. #5
    Forum Contributor
    Join Date
    07-27-2018
    Location
    Jodhpur, India
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 Pro 64-bit)
    Posts
    405

    Re: Need dyanmic Userform to split all worksheet based on specific headers and mail

    Dear VBA Expert,

    Please assist me with this post, as it is not a high priority. I can also split this article into smaller modules, but forum experts advise that if the method is lengthy, we should post the entire requirement post. It will help in the creation of the final code in a single step.

+ 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] split data based on three columns to multiple sheets and rename sheets based on headers
    By KalilMe in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-09-2021, 08:35 AM
  2. [SOLVED] Mail specific Worksheet from Excel considering specific criteria
    By RavindraK in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-02-2021, 07:16 AM
  3. Split worksheet into multiple worksheets with headers
    By kiwitech in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-30-2018, 07:05 AM
  4. [SOLVED] split numbers to to specific columns headers
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-14-2018, 10:39 AM
  5. [SOLVED] formula for multiple split headers of text and numbers to respective headers
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-19-2017, 10:17 AM
  6. Create Pivot with worksheet that has split/complex column headers?
    By sezza79 in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 07-08-2014, 06:45 AM
  7. [SOLVED] hide/undie untire column based on a dyanmic cell
    By hcyeap in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-30-2013, 03:35 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