+ Reply to Thread
Results 1 to 4 of 4

Creating a macro to split data into separate workbooks and separate worksheets

  1. #1
    Registered User
    Join Date
    12-06-2013
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Creating a macro to split data into separate workbooks and separate worksheets

    Hi - I have a data file that contains the following columns: Name, Region Name, Territory Name, Address, City, State, Zip

    I would like to create a macro that would break this data down into separate workbooks by region. There are 9 different regions. I would also like these 9 individual region files to split into multiple worksheets based on the amount of territories per region (i.e. the west region has 10 territories, so it would have 10 separate worksheets of data for the west region file).

    Attached is the file i have created so far. I have created the macro to split the files into regions and save them to a specific path on my desktop, however, i need the next step of breaking them into multiple worksheets per region file.

    I am looking to continue the code already written, however, I am open to any new code if its easier for the process. Any help would be appreciated! Thanks!

    Below is the code i have so far which is in the attached file.


    Sub Split_Data_in_workbooks()

    Application.ScreenUpdating = False

    Dim data_sh As Worksheet
    Set data_sh = ThisWorkbook.Sheets("Data")

    Dim setting_Sh As Worksheet
    Set setting_Sh = ThisWorkbook.Sheets("Settings")

    Dim nwb As Workbook
    Dim nsh As Worksheet

    ''''' Get unique Region

    setting_Sh.Range("A:A").Clear
    data_sh.AutoFilterMode = False
    data_sh.Range("B:B").Copy setting_Sh.Range("A1")

    setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes

    Dim i As Integer

    For i = 2 To Application.CountA(setting_Sh.Range("A:A"))

    data_sh.UsedRange.AutoFilter 2, setting_Sh.Range("A" & i).Value


    Set nwb = Workbooks.Add
    Set nsh = nwb.Sheets(1)

    data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy nsh.Range("A1")
    nsh.UsedRange.EntireColumn.ColumnWidth = 15

    nwb.SaveAs setting_Sh.Range("H6").Value & "/" & setting_Sh.Range("A" & i).Value & ".xlsx"
    nwb.Close False
    data_sh.AutoFilterMode = False
    Next i

    setting_Sh.Range("A:A").Clear

    MsgBox "Done"

    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Creating a macro to split data into separate workbooks and separate worksheets

    Hi there,

    See if the attached version of your workbook does what you need. It uses the following code:

    Please Login or Register  to view this content.
    The highlighted values may be altered to suit your requirements.


    The "child" workbooks are created in the same folder as the main workbook. If you are happy with this, the "Settings" worksheet is no longer required.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-06-2013
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Creating a macro to split data into separate workbooks and separate worksheets

    This is great! Thanks for the help Greg M!

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Creating a macro to split data into separate workbooks and separate worksheets

    Hi again,

    Many thanks for your very prompt feedback.

    You're welcome - glad I was able to help.

    Regards,

    Greg M


    P. S. Many thanks for the Reputation increase - much appreciated!
    Last edited by Greg M; 08-27-2019 at 11:42 AM. Reason: P. S. added

+ 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] Creating a macro to split data into separate workbooks
    By jfish07 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2019, 03:42 PM
  2. VBA to Split Worksheets into separate Workbooks by Tab Name
    By krissysteen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-26-2019, 01:56 PM
  3. [SOLVED] Split pairs of worksheets to separate workbooks
    By vhache in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-17-2016, 05:03 PM
  4. Split Data based on 3 Columns into separate worksheets
    By angeleenmc in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-29-2014, 12:40 PM
  5. Split data into separate worksheets and sort
    By jxm1092 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2013, 05:00 AM
  6. Split my data into separate worksheets
    By drunk8gods in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-12-2010, 04:20 PM
  7. [SOLVED] Macro to Split Workbook into separate Workbooks
    By jmurray in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-14-2009, 04:44 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