none
How to split data into multiple worksheet that already existed RRS feed

  • Question

  • Hi all,

    I would like to ask that is it possible to split data from main sheet into multiple sheets which already existed but have the same format?

    I'll give you the overview, I have many data in main sheet 1 which consist of data from month to date, then I run macro to split into multiple worksheet based on column that I want to split. Secondly, I have another main sheet (main sheet 2) which consist of data from year to date, then I want to do the exact way as before with macro to split data. But I want to combine data from year to date main sheet into worksheets that already consist of data from month to date. But I don't know how to write macro with the second condition like that. Could you guys help me on this?

    Thanks in advance.

    Thursday, July 5, 2018 2:23 AM

Answers

  • Here is a macro. Please test on a copy of your workbook.

    Warning: don't run the macro twice or more, for it will keep on adding data to the individual sheets.

    Sub SplitYTD()
        ' Change the constants as needed
        Const MainSheet = "Main Sheet 2" ' name of YTD sheet
        Const Col = "A" ' column to split on
        Const FirstRow = 1 ' Header row
        ' Variables
        Dim wsM As Worksheet
        Dim r0 As Long
        Dim r As Long
        Dim m As Long
        Dim strName As String
        Dim wsD As Worksheet
        ' The code
        Application.ScreenUpdating = False
        Set wsM = Worksheets(MainSheet)
        wsM.UsedRange.Sort Key1:=wsM.Cells(FirstRow, Col), Header:=xlYes
        r = FirstRow + 1
        Do
            If wsM.Cells(r, Col).Value = "" Then Exit Do
            If wsM.Cells(r, Col).Value <> wsM.Cells(r - 1, Col).Value Then
                r0 = r
                strName = wsM.Cells(r0, Col).Value
                Do While wsM.Cells(r + 1, Col).Value = strName
                    r = r + 1
                Loop
                Set wsD = Worksheets(strName) ' must exist, otherwise error
                m = wsD.Cells(wsD.Rows.Count, Col).End(xlUp).Row
                wsM.Range(wsM.Cells(r0, Col), wsM.Cells(r, Col)).EntireRow.Copy _
                    Destination:=wsD.Cells(m + 4, 1)
            End If
            r = r + 1
        Loop
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, July 5, 2018 9:14 AM
  • 1. I don't understand your first question. Can you explain what you want?

    2. You can create a macro that calls the 4 individual macros:

    Sub DoItAll()
        Call Macro1
        Call Macro2
        Call Macro3
        Call Macro4
    End Sub

    You have to substitute the actual names of the macros of course.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by h.fauzi Friday, July 6, 2018 8:11 AM
    Thursday, July 5, 2018 11:10 AM
  • Hello h.fauzi,

    >> Is there another way to include the title column to split with the data?

    You could add some code to copy the title row too. For instance,

     m = wsD.Cells(wsD.Rows.Count, Col).End(xlUp).Row
                'add code here to copy title row
                wsM.Cells(1, 1).EntireRow.Copy Destination:=wsD.Cells(m + 3, 1)
                wsM.Range(wsM.Cells(r0, Col), wsM.Cells(r, Col)).EntireRow.Copy _
                    Destination:=wsD.Cells(m + 4, 1)

    >>Also, is it possible to run 4 macros one by one with the conditions?

    This is not related to your original issue, please post a new thread for it.

    Best Regards,

    Terry


    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.

    • Marked as answer by h.fauzi Friday, July 6, 2018 8:13 AM
    Friday, July 6, 2018 5:28 AM

All replies

  • Is there overlap between the month to date and year to date data?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 5, 2018 7:31 AM
  • Hi Hans,

    Thank you for your reply.

    I want to put year to date data below month to date data separate only 3 rows, not overlap. The columns of both are exactly the same.

    Best Regards,

    Fauzi

    Thursday, July 5, 2018 8:37 AM
  • Here is a macro. Please test on a copy of your workbook.

    Warning: don't run the macro twice or more, for it will keep on adding data to the individual sheets.

    Sub SplitYTD()
        ' Change the constants as needed
        Const MainSheet = "Main Sheet 2" ' name of YTD sheet
        Const Col = "A" ' column to split on
        Const FirstRow = 1 ' Header row
        ' Variables
        Dim wsM As Worksheet
        Dim r0 As Long
        Dim r As Long
        Dim m As Long
        Dim strName As String
        Dim wsD As Worksheet
        ' The code
        Application.ScreenUpdating = False
        Set wsM = Worksheets(MainSheet)
        wsM.UsedRange.Sort Key1:=wsM.Cells(FirstRow, Col), Header:=xlYes
        r = FirstRow + 1
        Do
            If wsM.Cells(r, Col).Value = "" Then Exit Do
            If wsM.Cells(r, Col).Value <> wsM.Cells(r - 1, Col).Value Then
                r0 = r
                strName = wsM.Cells(r0, Col).Value
                Do While wsM.Cells(r + 1, Col).Value = strName
                    r = r + 1
                Loop
                Set wsD = Worksheets(strName) ' must exist, otherwise error
                m = wsD.Cells(wsD.Rows.Count, Col).End(xlUp).Row
                wsM.Range(wsM.Cells(r0, Col), wsM.Cells(r, Col)).EntireRow.Copy _
                    Destination:=wsD.Cells(m + 4, 1)
            End If
            r = r + 1
        Loop
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, July 5, 2018 9:14 AM
  • Thank you Hans, it works.

    I have another question,

    1. Is there another way to include the title column to split with the data?

    2. Also, is it possible to run 4 macros one by one with the conditions?

         > macro 1 is used to retrieve or update SQL data from database using connection with ODBC into main sheet 1 (consist of month to date data) 

         > macro 2 is used to retrieve or update SQL data from database using connection with ODBC into main sheet 2 (consist of year to date data) 

         > macro 3 is used to split main sheet 1 into multiple sheets

         > macro 4 is used to split main sheet 2 into multiple sheets from macro 3.

    or is there another way with use only 1 macro to cover all that and can be used again to update data every week? I have 2 SQL query, to retrieve month to date data and year to date data.

    Sorry for asking many questions.

    Thanks in advance.

    Best regards,

    Fauzi



    • Edited by h.fauzi Thursday, July 5, 2018 10:02 AM
    Thursday, July 5, 2018 10:01 AM
  • 1. I don't understand your first question. Can you explain what you want?

    2. You can create a macro that calls the 4 individual macros:

    Sub DoItAll()
        Call Macro1
        Call Macro2
        Call Macro3
        Call Macro4
    End Sub

    You have to substitute the actual names of the macros of course.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by h.fauzi Friday, July 6, 2018 8:11 AM
    Thursday, July 5, 2018 11:10 AM
  • Hi Hans,

    Sorry if my english was confusing.  It is not my first language.

    My first question is related to your macro. I have tried it and it worked, data were splitted into existing worksheets as I intended, but there was no title row. I also need title row in every worksheet from main sheet. So, there are title row for month to date data and title row for year to date data as well, but in separate table in worksheet, year to date data are below month to date data.

    Thank you.

    Best regards, 

    Fauzi



    • Edited by h.fauzi Friday, July 6, 2018 8:23 AM
    Friday, July 6, 2018 1:43 AM
  • Hello h.fauzi,

    >> Is there another way to include the title column to split with the data?

    You could add some code to copy the title row too. For instance,

     m = wsD.Cells(wsD.Rows.Count, Col).End(xlUp).Row
                'add code here to copy title row
                wsM.Cells(1, 1).EntireRow.Copy Destination:=wsD.Cells(m + 3, 1)
                wsM.Range(wsM.Cells(r0, Col), wsM.Cells(r, Col)).EntireRow.Copy _
                    Destination:=wsD.Cells(m + 4, 1)

    >>Also, is it possible to run 4 macros one by one with the conditions?

    This is not related to your original issue, please post a new thread for it.

    Best Regards,

    Terry


    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.

    • Marked as answer by h.fauzi Friday, July 6, 2018 8:13 AM
    Friday, July 6, 2018 5:28 AM
  • Hi Terry

    Thank you for your answer and help regarding this issue.

    This is my first time asking in this community, so thank you for your advice.

    Best regards,

    Fauzi



    • Edited by h.fauzi Friday, July 6, 2018 8:58 AM
    Friday, July 6, 2018 8:11 AM