+ Reply to Thread
Results 1 to 1 of 1

VBA to Split Worksheets into separate Workbooks by Tab Name

Hybrid View

  1. #1
    Registered User
    Join Date
    01-25-2018
    Location
    Michigan
    MS-Off Ver
    Microsoft 365 MSO (Version 2308 Build 16.0.16731.20496)
    Posts
    30

    VBA to Split Worksheets into separate Workbooks by Tab Name

    I have a workbook that has the following tabs:
    Assignments_CENTRL
    Assignments_NORTHR
    Assignments_PACIFC
    Assignments_SOUTHD
    Assignments_SOUTHJ
    SummaryAuditRptCENTRL
    SummaryAuditRptNORTHR
    SummaryAuditRptPACIFC
    SummaryAuditRptSOUTHD
    SummaryAuditRptSOUTHJ
    SummaryRptCENTRL
    SummaryRptNORTHR
    SummaryRptPACIFC
    SummaryRptSOUTHD
    SummaryRptSOUTHJ


    And I need them broken into separate workbooks by the last 6 for the tab name:
    File: CENTRL_CCYYMMDD
    TabNames: Assignments_CENTRL; SummaryAuditRptCENTRL; SummaryRptCENTRL

    File: NORTHR_CCYYMMDD
    Tab Names: Assignments_NORTHR, SummaryAuditRptNORTHR, SummaryRptNORTHR
    etc
    then possibly drop the last 6 of the tab names:
    File: CENTRL_CCYYMMDD
    TabNames: Assignments; SummaryAuditRpt; SummaryRpt

    This is the code i have so far:
    Sub Bridge_Optimizer6_Workbook_Creator()
    Dim SFilename As String
    Dim SPath As String
    Dim str, RegResult, TrimResult As String
    Dim RegionWB As Workbook
    Dim SOptimizerWB As Workbook
    Dim WSouter, WSinner As Worksheet
    Dim SOptimizerWS As String
    
    Set SOptimizerWB = ActiveWorkbook
    SOptWBCount = SOptimizerWB.Worksheets.Count
    
    For Each WSouter In SOptimizerWB.Sheets
        SOptimizerWS = WSouter.Name
        str = SOptimizerWS
        RegResult = Right(str, 6)
        TrimResult = Right(SOptimizerWB.Name, 20)
        SFilename = RegResult & "_" & TrimResult
        SPath = SOptimizerWB.Path
        Set RegionWB = Workbooks.Add
    
    RegionWB.SaveAs SPath & "\" & SFilename
    
    For Each WSinner In SOptimizerWB.Sheets
    If Right(WSinner.Name, 6) = RegResult Then
        WSinner.Move After:=RegionWB.Sheets(RegionWB.Sheets.Count)
    End If
    Next WSinner
    
    Next WSouter
    End Sub
    But I dont want a Sheet1 in each
    And our company is using Titus classification and it stops at Each file to ask classification
    Attached Files Attached Files
    Last edited by krissysteen; 06-27-2019 at 06:06 PM. Reason: adding my current code

+ 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] VBA to split document into separate workbooks
    By epidemik in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-03-2017, 11:48 AM
  2. [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
  3. Split multi-tab workbook into separate workbooks
    By ahurst105 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-28-2013, 06:51 PM
  4. [SOLVED] Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets
    By DHartwig35805 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-27-2012, 11:38 AM
  5. [SOLVED] Split table, based on two criteria, into separate workbooks
    By gingert88 in forum Excel General
    Replies: 6
    Last Post: 07-10-2012, 06:08 PM
  6. Split into Separate Worksheets
    By bigroo1958 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-15-2010, 10:54 AM
  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