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.
Bookmarks