+ Reply to Thread
Results 1 to 4 of 4

Splitting workbook

Hybrid View

Roadhouse Splitting workbook 07-04-2015, 04:14 PM
mehmetcik Re: Splitting workbook 07-04-2015, 05:56 PM
AlphaFrog Re: Splitting workbook 07-04-2015, 06:08 PM
xladept Re: Splitting workbook 07-04-2015, 06:13 PM
  1. #1
    Forum Contributor
    Join Date
    10-31-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    221

    Splitting workbook

    Hi I have an excel workbook with over 300 sheets, the workbook has a control sheet then starts from sheet 2. I want to copy the workbook into 30 different workbooks so the new workbooks would have a control sheet then 10 sheets from the workbook with 300 sheets.

    workbook 1 control sheet, sheet 2-11
    workbook2 control sheet, sheet 12-21

    etc

    Can anyone help me with this?

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Splitting workbook

    
    Sub Macro3()
    MyName = ActiveWorkbook.Name
    MyPath = ActiveWorkbook.Path
    MyCompletePath = ActiveWorkbook.FullName
    
    Sheets("Control Sheet").Move Before:=Sheets(1)
        
    Count = 1
    MySheetsCount = ActiveWorkbook.Sheets.Count - 1
    Totalcount = 1
    LoopStart:
        Sheets("Control Sheet").Copy
        ChDir "C:\Users\Mehmet\Application Data\Desktop"
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\Mehmet\Application Data\Desktop\Extract" & Count & ".xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
    
    For Sheetcount = 1 To 10
        Windows(MyName).Activate
        Sheets(2).Move After:=Workbooks("Extract1.xlsx").Sheets(Sheetcount)
        Totalcount = Totalcount + 1
        If Totalcount > MySheetsCount Then GoTo Done
      
    Next
    Count = Count + 1
    GoTo LoopStart
        
    Done:
    
    End Sub
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Splitting workbook

    Another one...

    Sub Separate_Sheets()
        Dim i As Long, j As Long, wb As Workbook
        Application.ScreenUpdating = False
        For i = 2 To ThisWorkbook.Worksheets.Count Step 10
            ThisWorkbook.Sheets(1).Copy
            Set wb = ActiveWorkbook
            For j = 0 To 9
                If i + j <= ThisWorkbook.Sheets.Count Then
                    ThisWorkbook.Sheets(i + j).Copy After:=wb.Sheets(wb.Sheets.Count)
                Else
                    Exit For
                End If
            Next j
        Next i
        Application.ScreenUpdating = True
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Splitting workbook

    Hi Roadhouse,

    You need to have your Master Book Active and it probably requires some tweaking

    Sub Roadhouse(): Dim i As Long, n As Long, WM As Workbook, wc As Worksheet, ws As Worksheet
    Dim x As Long, S As String: S = ActiveWorkbook.FullName: i = InStrRev(S, "."): S = Left(S, i - 1)
    Set WM = ActiveWorkbook: Set wc = WM.Sheets(1): i = 2: For n = 1 To 30
                    wc.copy: ActiveWorkbook.SaveAs S & "_" & n & ".xlsx"
                    For x = 1 To 10
                    Worksheets.Add(After:=Worksheets(Worksheets.Count)).name = x
                        WM.Sheets(i).Cells.copy Range("A1"): i = i + 1
                    Next x
                    Application.DisplayAlerts = False
                    ActiveWorkbook.Close True
                    Application.DisplayAlerts = True
    Next n
    End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ 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. Splitting data into new workbook
    By Apsank1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2012, 09:14 PM
  2. Splitting data into many workbook using a ID
    By a.hussain in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-22-2012, 02:49 PM
  3. Workbook Sharing and Text Splitting
    By rakesh14021983 in forum Excel General
    Replies: 3
    Last Post: 01-10-2009, 11:54 AM
  4. [SOLVED] Splitting a workbook
    By Tony Vella in forum Excel General
    Replies: 2
    Last Post: 08-14-2006, 04:55 PM
  5. Splitting a workbook
    By MarkN in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2006, 01:20 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