Conditionally split single sheet into Multiple sheets -

---

Useful at work segregation

---

Please Note: I have executed the code and placed over here, so, please follow steps give under Sample Code header, and try to execute the program, if it works well, then to read the complete article for understanding as to how it works, else, it is waste of your time.
Let us first consider a situation where you have 10,000 rows, as shown in screen-shot below. The data can be split based on 'Entry Date' Col A or on 'Customer Name' Col B or on 'Customer Number' Col C and so on... ie., in a general situation, where ever we have common data or repeatedly occuring data, we need to split the data.

Sample Data in picture:

• I used this data to split the data based on Customer Name.
• the same method can be applied where ever there is a duplicate

---

Examples: when a spread sheet is to be split by bank name or employee name or group name or region name or pin code etc...

Procedure to adopt

This can be done in many ways, but, I adopt the following method, which is fairly simple:

• Pre-Processing:
  • Sort the Data: Ensure the 'RawData' sheet is sorted by the **required column** (e.g., Column B - Customer Name) based on which you intend to split the data.
• Initialization:
  • Define a variable for the **Start Row** (e.g., lngStartRow) and initialize it to the row *after* the header row (e.g., row 2, assuming row 1 is the header).
  • Define a variable for the **End Row** (e.g., lngEndRow).
  • Define a variable to store the **current Customer Name** (e.g., strCustomerName).
  • Determine the **Last Used Row** in the 'RawData' sheet (e.g., lngLastRow).
• Looping and Splitting:
  • Start a **loop** that iterates through the rows of the 'RawData' sheet, beginning from the row after the header (e.g., row 2) up to the lngLastRow.
  • Check for Value Change: Inside the loop, check if the value in the Customer Name column (Column B) of the **current row** is *different* from the value in the Customer Name column of the **previous row** (or if it's the first data row).

    In your example, the "change points" are rows 6, 10, 13, 17, 18. The actual split logic triggers when the value *changes* from the previous one.

  • Set End Row and Customer Name:
    • If a change is detected (or at the end of the data):
      • Set the lngEndRow to the **previous row's number** (i.e., the current row number - 1).
      • Set the strCustomerName to the value in the Customer Name column (Column B) of the **previous row**.
    • Handle the Initial Segment:
      • The very first segment will be processed when the value changes for the first time (e.g., at row 6). Before the first change, the segment starts at lngStartRow (e.g., 2) and ends at the row just before the change (e.g., row 5). The strCustomerName will be the name in cell B2.
  • Data Selection:
    • Select the range of data in the 'RawData' sheet from row lngStartRow to lngEndRow (for all columns you need).
  • Sheet Creation and Renaming:
    • Create a New Sheet.
    • Rename the new sheet using the value stored in the strCustomerName variable.
  • Copy Header Row:
    • Select the **Header Row** data from the 'RawData' sheet (i.e., Row 1).
    • Copy this data.
    • Paste the copied content onto the **first row (A1 onwards)** of the newly created sheet.
  • Paste Data:
    • Copy the previously selected data (from lngStartRow to lngEndRow) from the 'RawData' sheet.
    • Paste this copied content onto cell **A2** of the new sheet.
  • Update Start Row:
    • Set the new lngStartRow to the **current row number** (where the value change was detected), preparing for the next segment.
  • Handle the Last Segment:
    • After the loop finishes, a final check is needed to process the last segment of data from the last updated lngStartRow up to the lngLastRow. The logic for selection, sheet creation, and pasting must be repeated for this final block.

To Execute the following code

---

• Excel Setup:
  • Open **Ms. Excel** (Version 2010 or later).
  • By default, you get three sheets (Sheet1, Sheet2, Sheet3).
  • **Delete** Sheet2 and Sheet3.
  • **Rename** Sheet1 to RawData.
  • **Prepare sample data** in the RawData sheet (including the Customer Name column, Column B, which will be the basis for splitting).
• Accessing the Visual Basic Editor (VBE):
  • Press the keyboard shortcut **Alt + F11** to open the Visual Basic Editor.
• Code Insertion:
  • In the VBE's Project Explorer (usually on the left), **double-click** on the 'RawData' sheet object (which is likely named Sheet1 (RawData)). This opens the code window for that specific sheet.
  • **Copy the VBA code** (the procedure for splitting the data) and **paste** it into the code window you just opened.
• Compiling and Testing the Code:
  • Press the keyboard shortcut **Alt + D** and then **L** (or navigate to Debug > Compile VBAProject) to compile the code and check for syntax errors.
• Step-by-Step Execution (Debugging):
  • In the VBA code window, ensure your cursor is placed inside the procedure (macro) you want to run (e.g., Sub Procedure1()).
  • Constantly press the **F8 key** to execute the code line-by-line (Step Into). This allows you to check the execution flow, variable values, and the resulting output on the Excel workbook.

Expected OutCome..

---