locked
Excel Macro - Split mastersheet into severeal sub-sheets with multiple conditions RRS feed

  • Question

  • Hey all,

    I have a table in excel which contains information about car leasing contracts. I need to split them by dealer, Vehicle Segment (Passenger car, van, other), and by used/new. Since there are 8 dealers, 3 segments and 2 factors used and new --> I end up with 8*3*2= 48 sheets. (luckily not all dealers sell all segments so it's actually less)

    Is there a macro for that kind of split? Splitting a mastersheet into several sheets based on multiple conditions? Sadly I'm not a programmer at all!!

    Thanks in advance!

    Thursday, September 13, 2018 8:38 AM

All replies

  • With your worksheet setup, is there a column that identifies each of the criteria something like the following screen shot? The columns can be in any order with other intermediate columns.

    My initial thoughts are to Use AutoFilter to display each set of data and then the visible data can be copied to another worksheet.

    How do you intend naming the worksheets. Suggestion abbrev "Dealer name-Car-used", "Dealer name-Other-New" etc where the name is created with the AutoFilters set for the data. They do not have to be this way but there must be some rules for naming the worksheets. It could be a matrix table on a separate worksheet with the sheet name and data columns to include on the sheet.

    If required, some of the columns on the master sheet can be excluded on each individual sheet provided you supply the rules.

    It will be much easier if you upload a copy of your workbook to OneDrive. If there is sensitive data then just make a copy of the workbook and either delete the sensitive data or replace it with dummy data. eg. Dealer names can be replaced with Name1, Name2 etc.

    1. Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)
    2. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    3. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    4. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    5. Go to this link.  https://onedrive.live.com
    6. Use the same login Id and Password that you use for this forum.
    7. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    8. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    9. Right click the file on OneDrive and select Share.
    10. Select "Get a Link" from the popup menu.
    11. Click in the field displaying the link and Ctrl and A should highlight the entire link and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

    Regards, OssieMac

    Friday, September 14, 2018 7:22 AM
  • Hi PhilTheController,

    Unfortunately, I can only create 8 sheets that named as 8 dealers for you.

    To generate 48 or less sheets, you should change the column in macro and run the code in every 8 sheets including the sheets that they generate(every as a master sheet to run the code, just like the first one).

    Please try the VBA code as below:

    Sub SplitData()
        Const NameCol = "A" 'you can change the column "A" to "B" or "C" to select which column do you want to use to generate a new sheet
        Const HeaderRow = 1
        Const FirstRow = 2
        Dim SrcSheet As Worksheet
        Dim TrgSheet As Worksheet
        Dim SrcRow As Long
        Dim LastRow As Long
        Dim TrgRow As Long
        Dim dealer As String
        Application.ScreenUpdating = False
        Set SrcSheet = ActiveSheet
        LastRow = SrcSheet.Cells(SrcSheet.Rows.Count, NameCol).End(xlUp).Row
        For SrcRow = FirstRow To LastRow
            dealer = SrcSheet.Cells(SrcRow, NameCol).Value
            Set TrgSheet = Nothing
            On Error Resume Next
            Set TrgSheet = Worksheets(dealer)
            On Error GoTo 0
            If TrgSheet Is Nothing Then
                Set TrgSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                TrgSheet.Name = dealer
                SrcSheet.Rows(HeaderRow).Copy Destination:=TrgSheet.Rows(HeaderRow)
            End If
            TrgRow = TrgSheet.Cells(TrgSheet.Rows.Count, NameCol).End(xlUp).Row + 1
            SrcSheet.Rows(SrcRow).Copy Destination:=TrgSheet.Rows(TrgRow)
        Next SrcRow
        Application.ScreenUpdating = True
    End Sub

    I uploaded the excel to my oneDrive, you can get it via this link.

    If you want to learn more about VBA coding, please review the following link:

    Getting Started with VBA in Office

    Hopefully it helps you. Please feel free to ask any questions.

    Best Regards,

    Yuki


    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.


    Friday, September 14, 2018 10:18 AM
  • If the reply by Yuki Lou-MSFT satisfies your requirements then I'll step out of this otherwise I believe that I can achieve the full report with all worksheets if you can upload a copy of your workbook.

    Regards, OssieMac

    Friday, September 14, 2018 11:06 PM
  • Hi PhilTheController,

    Thanks for your asking. Did you resolve your issue ? If the post helps you, you can mark it as answer and close the post.

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

    I wish you a happy life!

    Best Regards,

    Yuki


    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.

    Friday, September 21, 2018 8:42 AM