Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: 10$ For VBA Code Split Data To Multiple Workbook Based On Unique Values In A Column

  1. #11
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    i found the problem. it is becuase the source workbook file format is .xlsb and once i changed it to .xls then it worked.

    all of my source workbooks are originally xlsb is there anyway that the code will work in xlsb source workbook?

  2. #12
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    i also checked with other file formats it does not work in xlsm as well. it only works in .xls seems like the code only functions in old version of excel only.

  3. #13
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    IT's working fine for me in the binary format (xlsb).

    I've made a small modification. try this

    Code:
    Sub SplitWorkbook()
    
        Dim colLetter As String, SavePath As String
        Dim lastValue As String
        Dim wb As Workbook
        Dim lng As Long
        Dim currentRow As Long
        colLetter = "P"
        SavePath = "" 'Indicate the path to save
        If SavePath = "" Then SavePath = ThisWorkbook.Path
        'Sort the workbook.
        With ThisWorkbook.Worksheets(1)
            .Sort.SortFields.Add Key:=.Range(colLetter & ":" & colLetter), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange .Parent.UsedRange.Cells
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            For lng = 2 To .Range(colLetter & .Rows.Count).End(xlUp).Row
                If .Cells(lng, colLetter).Value = "" Then Exit For
                lastValue = .Cells(lng, colLetter).Value
                .Cells.AutoFilter field:=.Cells(lng, colLetter).Column, Criteria1:=lastValue
                lng = .Cells(.Rows.Count, colLetter).End(xlUp).Row
                Set wb = Application.Workbooks.Add(xlWorksheet)
                .Rows(1 & ":" & lng).Copy wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp)
                wb.SaveAs SavePath & "\" & lastValue, 50
                wb.Close
            Next
            .AutoFilterMode = False
        End With
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #14
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    i use excel 2010

  5. #15
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    It's more to do with your available system resources, than anything else I think. Can you try the same macro on a more powerful computer?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #16
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    the modified code you provided, worked perfectly.

    now only one issue that all the generated workbooks have the default worksheet name as "sheet1" i want the worksheet name to be the same as the source worksheet which is "GL_Details" for all those generated workbooks the worksheet name should be "GL_Details"

    can you please help on this part of the code?

    Quote Originally Posted by Excel Fox View Post
    It's more to do with your available system resources, than anything else I think. Can you try the same macro on a more powerful computer?

  7. #17
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    The reason why the modified code worked is because I reduced the range of cells to just the used range of the sheet. So it's your available resources that's cause the trouble. Having said that, I should have only used the used range instead of the entire cells.

    To add a name to the sheet, just add the below line, right after the workbook is added

    Code:
    wb.Sheets(1).Name = "NameOfSheetHere"
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #18
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    thank you very much. i do not even know your name. but i i guess that our messages have crossed before in mrexcel forums.

    so, please give pm me your paypal ID so that i send you 10 bucks.

    also looking forward for solution to my outlook project in the other thread.


    Quote Originally Posted by Excel Fox View Post
    The reason why the modified code worked is because I reduced the range of cells to just the used range of the sheet. So it's your available resources that's cause the trouble. Having said that, I should have only used the used range instead of the entire cells.

    To add a name to the sheet, just add the below line, right after the workbook is added

    Code:
    wb.Sheets(1).Name = "NameOfSheetHere"

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 06-12-2023 at 01:04 PM.

Similar Threads

  1. Replies: 10
    Last Post: 05-23-2013, 12:30 PM
  2. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  3. Replies: 2
    Last Post: 04-14-2013, 09:15 PM
  4. Replies: 2
    Last Post: 03-05-2013, 07:34 AM
  5. Group Pivot Data Based On Row Values In One Column
    By mrmmickle1 in forum Excel Help
    Replies: 10
    Last Post: 10-09-2012, 11:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •