none
Macro for splitting rows into different sheets in excel RRS feed

  • Question

  • Hi Team,

    can anyone assist me in setting up a macro to split bulk rows in an excel sheet into different sheets of 200 lines each?

    Regards,

    Binu Joseph89



    Friday, April 6, 2018 2:02 PM

All replies

  • Binu,
    re: data to multiple sheets

    '---
    Sub EmptyTheBag()
    'Jim Cone - April 2018 - https://goo.gl/IUQUN2
     Dim N As Long
     Dim S As Long
     Dim LastRow As Long
     
     S = 2
     LastRow = Worksheets(1).Cells(Worksheets(1).Rows.Count, 1).End(xlUp).Row
     For N = 201 To LastRow Step 200
       With Worksheets(1)
         .Range(.Rows(N), .Rows(N + 199)).Cut Destination:=Worksheets(S).Range("A1")
       End With
       S = S + 1
       If S > Worksheets.Count Then
         Exit For
       ElseIf N + 199 > LastRow Then
         Exit For
       End If
     Next
    End Sub
    '---

    Jim Cone
    https://goo.gl/IUQUN2

    • Edited by James Cone Friday, April 6, 2018 8:31 PM added worksheets to last row
    Friday, April 6, 2018 8:26 PM
  • Hi Jim,

    thanks for the response. But the macro is not working and is showing the following underlined error:

    Sub EmptyTheBag()
    'Jim Cone - April 2018 - https://goo.gl/IUQUN2
     Dim N As Long
     Dim S As Long
     Dim LastRow As Long
     
     S = 2
     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
     For N = 201 To LastRow Step 200
       With Worksheets(1)
         .Range(.Rows(N), .Rows(N + 199)).Cut Destination:=Worksheets(S).Range("A1")
       End With
       S = S + 1
       If S > Worksheets.Count Then
         Exit For
       ElseIf N + 199 > LastRow Then
         Exit For
       End If
     Next
    End Sub

    Please have a look and if you need information from the workbook please let me know.  I will have 7-8 columns which are in the data range.

    Regards,

    Binu Joseph

    Friday, April 6, 2018 8:38 PM
  • also the name of the sheet from which the data is going to be retrieved is "Sheet1"

    Friday, April 6, 2018 8:39 PM
  • Binu,
    re:  error

    The code does not add any worksheets.  You have to do that before running the code.
    The code uses the first sheet in the workbook as the data sheet.  The name doesn't matter.
    Also, there may be some rows left at the bottom of the first sheet, if the data is not a multiple of 200.

    '---
    Jim Cone
    Friday, April 6, 2018 8:52 PM
  • Hi Jim,

    it does not run.

    Thanks.

    Binu Joseph

    Friday, April 6, 2018 9:36 PM
  • Binu,
    re:  does not run

    For you, it runs for me.
    The data must start in Column 1.
    Also, below is a slightly modifed version of the code that ensures all data is transferred.
    (no straggler rows)
    '---
    Jim Cone

    '---
    Sub EmptyTheBag_R1()
    'Jim Cone - April 2018 - https://goo.gl/IUQUN2
     Dim N As Long
     Dim S As Long
     Dim LastRow As Long
     
     S = 2
     LastRow = Worksheets(1).Cells(Worksheets(1).Rows.Count, 1).End(xlUp).Row
     LastRow = Application.WorksheetFunction.Ceiling(LastRow, 200)
     For N = 201 To LastRow Step 200
       With Worksheets(1)
         .Range(.Rows(N), .Rows(N + 199)).Cut Destination:=Worksheets(S).Range("A1")
       End With
       S = S + 1
       If S > Worksheets.Count Then
         Exit For
       End If
     Next
    End Sub
    '---

    Friday, April 6, 2018 10:15 PM
  • Hi Binu Joseph89,

    Try to refer example below.

    Currently, In Shee1 contains 100 rows. Below code will split data to new rows 10 rows in each new sheet.

    Sub demo()
    Dim lastRow As Long, myRow As Long, mySheet As Worksheet
    lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    For myRow = 2 To lastRow Step 10
        Set mySheet = Worksheets.Add
        Sheets("Sheet1").Rows(myRow & ":" & myRow + 9).EntireRow.Copy mySheet.Range("A1")
    Next myRow
    End Sub
    

    Output:

    Further, You just need to modify this example to meet your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 9, 2018 3:19 AM
    Moderator
  • Hi Binu Joseph89,

    Is your issue solved?

    I find that you did not follow up this for a long time.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 19, 2018 9:39 AM
    Moderator