Results 1 to 10 of 10

create multiple work books with multiple tabs

Threaded View

  1. #1
    Registered User
    Join Date
    10-18-2013
    Location
    Wayne PA
    MS-Off Ver
    Excel 2010
    Posts
    6

    create multiple work books with multiple tabs

    I am trying to setup a macro that will take my original file that includes multiple sheets and split it into multiple workbooks with the same sheets based on a name entered into a column on the first (main) sheet. I have been able to create a macro that will create multiple workbooks for one sheet but not for multiple sheets (see below). The main worksheet will contain unique data, but the other worksheets will just need to be copied to the new workbooks. I also need any links on the main worksheet to remain intact (linked to other sheets within the workbook as opposed to linking to the original workbook). Pleaee help if you can. Thank you.



    Public Sub SplitToFiles()
    
    ' Sort column D
    
        Rows("10:10").Select
        Range(Selection, Selection.End(xlDown)).Select
        ActiveWorkbook.Worksheets("Input Sheet").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Input Sheet").Sort.SortFields.Add Key:=Range( _
            "D10:D100000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Input Sheet").Sort
            .SetRange Range("A10:ZZ100000")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("A9").Select
        
    
    ' MACRO SplitToFiles
    ' Last update: 2013-10-11
    ' Author: jbarton
    ' Version 1.2
    ' Description:
    ' Loops through a specified column, and split each distinct values into a separate file by making a copy and deleting rows below and above
    '
    ' Note: Values in the column should be unique or sorted.
    '
    ' The following cells are ignored when delimiting sections:
    ' - blank cells, or containing spaces only
    ' - same value repeated
    ' - cells containing "total"
    '
    ' Files are saved in a "Split" subfolder from the location of the source workbook, and named after the section name.
    
    Dim osh As Worksheet ' Original sheet
    Dim iRow As Long ' Cursors
    Dim iCol As Long
    Dim iFirstRow As Long ' Constant
    Dim iTotalRows As Long ' Constant
    Dim iStartRow As Long ' Section delimiters
    Dim iStopRow As Long
    Dim sSectionName As String ' Section name (and filename)
    Dim rCell As Range ' current cell
    Dim owb As Workbook ' Original workbook
    Dim sFilePath As String ' Constant
    Dim iCount As Integer ' # of documents created
    
    iCol = 4
    iRow = 10
    iFirstRow = iRow
    
    Set osh = Application.ActiveSheet
    Set owb = Application.ActiveWorkbook
    iTotalRows = osh.UsedRange.Rows.Count
    sFilePath = Application.ActiveWorkbook.Path
    
    If Dir(sFilePath + "\Comp File Split", vbDirectory) = "" Then
        MkDir sFilePath + "\Comp File Split"
    End If
    
    'Turn Off Screen Updating  Events
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Do
        ' Get cell at cursor
        Set rCell = osh.Cells(iRow, iCol)
        sCell = Replace(rCell.Text, " ", "")
    
        If sCell = "" Or (rCell.Text = sSectionName And iStartRow <> 0) Or InStr(1, rCell.Text, "total", vbTextCompare) <> 0 Then
            ' Skip condition met
        Else
            ' Found new section
            If iStartRow = 0 Then
                ' StartRow delimiter not set, meaning beginning a new section
                sSectionName = rCell.Text
                iStartRow = iRow
            Else
                ' StartRow delimiter set, meaning we reached the end of a section
                iStopRow = iRow - 1
    
                ' Pass variables to a separate sub to create and save the new worksheet
                CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, owb.fileFormat
                iCount = iCount + 1
    
                ' Reset section delimiters
                iStartRow = 0
                iStopRow = 0
    
                ' Ready to continue loop
                iRow = iRow - 1
            End If
        End If
    
        ' Continue until last row is reached
        If iRow < iTotalRows Then
                iRow = iRow + 1
        Else
            ' Finished. Save the last section
            iStopRow = iRow
            CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, owb.fileFormat
            iCount = iCount + 1
    
            ' Exit
            Exit Do
        End If
    Loop
    
    'Turn On Screen Updating  Events
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    MsgBox Str(iCount) + " documents saved in " + sFilePath
    
    
    End Sub
    Public Sub DeleteRows(targetSheet As Worksheet, RowFrom As Long, RowTo As Long)
    
    Dim rngRange As Range
    Set rngRange = Range(targetSheet.Cells(RowFrom, 1), targetSheet.Cells(RowTo, 1)).EntireRow
    rngRange.Select
    rngRange.Delete
    
    End Sub

    Public Sub CopySheet(osh As Worksheet, iFirstRow As Long, iStartRow As Long, iStopRow As Long, iTotalRows As Long, sFilePath As String, sSectionName As String, fileFormat As XlFileFormat)
         Dim ash As Worksheet ' Copied sheet
         Dim awb As Workbook ' New workbook
    
         ' Copy book
         osh.Copy
         Set ash = Application.ActiveSheet
    
         ' Delete Rows after section
         If iTotalRows > iStopRow Then
             DeleteRows ash, iStopRow + 1, iTotalRows
         End If
    
         ' Delete Rows before section
         If iStartRow > iFirstRow Then
             DeleteRows ash, iFirstRow, iStartRow - 1
         End If
    
         ' Select left-topmost cell
         ash.Cells(1, 1).Select
    
         ' Clean up a few characters to prevent invalid filename
         sSectionName = Replace(sSectionName, "/", " ")
         sSectionName = Replace(sSectionName, "\", " ")
         sSectionName = Replace(sSectionName, ":", " ")
         sSectionName = Replace(sSectionName, "=", " ")
         sSectionName = Replace(sSectionName, "*", " ")
         sSectionName = Replace(sSectionName, ".", " ")
         sSectionName = Replace(sSectionName, "?", " ")
    
         ' Save in same format as original workbook
         ash.SaveAs sFilePath + "\Comp File Split\" + sSectionName, fileFormat
    
         ' Close
         Set awb = ash.Parent
         awb.Close SaveChanges:=False
    End Sub
    Last edited by arlu1201; 10-19-2013 at 02:09 AM. Reason: Use code tags as per rule 3.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro for merging multiple work books
    By sridileep in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-21-2013, 01:37 AM
  2. Excel 2007 : merge Multiple Work Books
    By stinkyrob in forum Excel General
    Replies: 0
    Last Post: 10-21-2009, 03:03 PM
  3. Copying sheet 1 from multiple books into tabs on master
    By jaikin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2009, 08:49 AM
  4. Replies: 0
    Last Post: 12-19-2005, 07:45 PM
  5. Histogram made from multiple exel work books.
    By frank in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-16-2005, 04:05 PM

Tags for this Thread

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