+ Reply to Thread
Results 1 to 3 of 3

Splitting multiple worksheet

  1. #1
    Registered User
    Join Date
    04-03-2015
    Location
    london
    MS-Off Ver
    excel 2019
    Posts
    21

    Splitting multiple worksheet

    1.I have this macro for splitting multiple worksheet , but if I freeze top 2 rows as header instead of 3 define in macro 1 blank row gets created I want to delete that row or can it be flexible

    2.if I want to change the criteria column for splitting where should I change now it is column 1 can it be flexible

    3.after spliting the file format of the file gets change I want the preserved the same format

    Please Login or Register  to view this content.

    i have posted this on other forum

    https://www.mrexcel.com/forum/excel-...worksheet.html

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Splitting multiple worksheet

    please see comments
    [CODE][Sub test()
    Dim ws As Worksheet, wb As Workbook
    Dim a, e, i As Long, ii As Long, w, x
    With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For Each ws In Worksheets
    a = ws.UsedRange.Value
    ReDim w(1 To UBound(a, 2))
    For i = 4 To UBound(a, 1) 'this is the first row, change to 3
    If a(i, 1) = "" Then Exit For '.if I want to change the criteria column for splitting where should I change now it is column 1 can it be flexible

    If Not .exists(a(i, 1)) Then
    Set .Item(a(i, 1)) = CreateObject("Scripting.Dictionary")
    End If
    If Not .Item(a(i, 1)).exists(ws.Name) Then
    ReDim x(1 To 2)
    Set x(1) = CreateObject("System.Collections.ArrayList")
    Set x(2) = ws.UsedRange.Rows("1:2")
    .Item(a(i, 1))(ws.Name) = x
    End If
    For ii = 1 To UBound(a, 2)
    w(ii) = a(i, ii)
    Next
    .Item(a(i, 1))(ws.Name)(1).Add w
    Next
    Next
    For Each e In .keys
    Set wb = Workbooks.Add
    For i = 0 To .Item(e).Count - 1
    If i + 1 > wb.Sheets.Count Then
    wb.Sheets.Add after:=wb.Sheets(wb.Sheets.Count)
    wb.Sheets(wb.Sheets.Count).Name = .Item(e).keys()(i)
    Else
    wb.Sheets(i + 1).Name = .Item(e).keys()(i)
    End If
    w = Application.Index(.Item(e).items()(i)(1).ToArray, 0, 0)
    .Item(e).items()(i)(2).Copy wb.Sheets(.Item(e).keys()(i)).Cells(1)
    wb.Sheets(.Item(e).keys()(i)).[a4] _
    .Resize(UBound(w, 1), UBound(w, 2)).Value = w
    Next
    wb.SaveAs ThisWorkbook.Path & "\" & e & ".xlsx" 'after spliting the file format of the file gets change I want the preserved the same format
    wb.Close
    Next/CODE]

  3. #3
    Registered User
    Join Date
    04-03-2015
    Location
    london
    MS-Off Ver
    excel 2019
    Posts
    21

    Re: Splitting multiple worksheet

    error reflecting code code cannot run in break mode

+ 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 multiple worksheet based on column criteria
    By sachin483 in forum Excel General
    Replies: 2
    Last Post: 04-10-2015, 10:27 PM
  2. Replies: 14
    Last Post: 08-20-2014, 05:46 AM
  3. Need help with worksheet splitting.
    By K-Usagi in forum Excel General
    Replies: 1
    Last Post: 05-14-2014, 04:47 PM
  4. Excel worksheet splitting into multiple worksheets with folder save
    By utechtj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-30-2014, 02:36 AM
  5. [SOLVED] splitting worksheet into multiple worksheets
    By mmdecarl in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-17-2013, 02:35 PM
  6. Splitting 2K worksheet into multiple worksheets
    By abmayfield in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-18-2012, 04:28 PM
  7. Replies: 0
    Last Post: 03-01-2005, 05:05 PM

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