File Splitter Add-In

  • Hi,

    Recently left a job where someone kindly created a 'file cutter' add in for our dept, and would appreciate any help in recreating this. It basically cut one sheet into separate workbooks based values in a single column, and also gave the option to include other (untouched) tabs in the new workbook - e.g. sheets with tables or charts that link to the original sheet data.

    The tool worked in this order:

    1. First prompt box opened, asking user to select

    a. The sheet to cut

    b. The column to cut the sheet by

    c. The header row

    d. First row of data (defaulted to the row below 1.c.)

    e. Last row of data (defaulted to the last row containing data in the sheet)

    f. The other tabs in the file to copy to the new workbook (listing every other sheet, hidden or otherwise, present in the workbook)

    2. Second prompt box opened, asking user to select

    a. Which values in the column selected in 1.b. to produce cuts of

    b. The naming convention of the new workbooks - two text boxes in following format: <free text> & [cut name] & <free text>

    c. The password to apply to each new workbook

    3. Start button would then trigger the VBA to run the following process - all happening in the background (i.e. not visible):

    a. Save the file

    b. Save the file with the naming convention in 2.b. for the first cut

    c. Clear all filters in sheet to cut (1.a.)

    d. Filter and delete the non-relevant rows, then clear filters

    e. Delete any tabs not selected in 1.a. & 1.f.

    f. Save the file with the password defined in 2.c., then close the file

    g. Reopen the original file, and repeat from 3.a., until all values selected in 2.a. have had a new workbook created for them

    h. End

  • Here's some basic code.

    Your requirement is achievable but really not a Forum question. Have you searched for an addin?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!