VBA & Macro Problems

How to Split Excel Data by Bin Location Using a VBA Macro

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs a VBA macro to split inventory data from a main sheet into multiple count sheets, capping at 75 rows per sheet, while ensuring that rows with identical bin locations in Column J are kept together.

Product: Excel
Device & OS: not provided
Scenario: Organizing large sets of inventory or warehouse data into manageable count sheets without breaking up items located in the same bin.
Observed behavior: The macro needs to group rows and copy specific columns (A-C and I-J) successfully, but some destination sheets experience incorrect values and formatting changes in column J after the split.

Before you start

Before running or editing macros, ensure your Developer tab is enabled in the ribbon. It is highly recommended to save a copy of your workbook, as VBA macro actions cannot be undone using the standard undo button.

Solution 1Recommended

Use a VBA Macro with Value2 and Text Formatting

Implement a VBA macro designed to group rows logically, checking bin locations before splitting, and utilizing the Value2 property to ensure data transfers without formatting errors.

To accomplish this, your macro needs a loop that tracks the row count. Before making a split at row 75, it must check if the bin location in Column J of the current row matches the next row. If they match, it should include the next row to prevent splitting the bin across two sheets.

To solve the issue with incorrect values and formatting changes during the copy process, you should write the array using Value2 and explicitly set the destination column format to Text.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click 'Insert' from the top menu, then select 'Module' to create a blank script window.

Draft the Grouping Logic

Write your loop to iterate through the 'BISA ALL' sheet. Use a counter to track rows up to 75. Include an 'If' statement to check if 'Cells(i, 10).Value = Cells(i + 1, 10).Value'. If true, continue adding to the current array batch to keep identical bins together.

Format Destination as Text

Before pasting the data into the count sheets, add a line to format Column J as text to prevent Excel from changing the values. Use: 'Sheets("Count 1").Range("J:J").NumberFormat = "@"'.

Write Data using Value2

When outputting the copied columns (A-C and I-J) to the destination sheets, assign the array using '.Value2' instead of '.Value' (e.g., 'DestinationRange.Value2 = SourceArray'). This bypasses Excel's auto-formatting for raw data transfers.

Why use Value2?: Using .Value2 instead of .Value is not only faster for processing large datasets, but it also prevents Excel from misinterpreting alphanumeric strings, dates, or currency formats during the copy-paste operation.

Advanced Spreadsheets with WPS Office

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers excellent support for VBA macros, allowing you to automate complex tasks like data splitting, inventory grouping, and batch processing just as easily as you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open your .xlsm file containing the 'BISA ALL' sheet.
2. Access the Developer tab: Navigate to the 'Developer' tab located on the top ribbon menu.
3. Open the VBA Editor: Click on 'VBA Editor' to write, paste, or modify your data splitting macro.
4. Execute the Macro: Run your script to automatically generate the count sheets with correctly grouped bin locations.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) filesBuilt-in VBA/Macro editor for advanced data automationFast processing capabilities for large inventory datasetsFamiliar ribbon interface ensures zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro alter the formatting of copied data?

When VBA copies data using the standard .Value property, Excel attempts to interpret the data format (like dates or numbers). Using the .Value2 property prevents this interpretation, keeping the raw underlying data perfectly intact.

How can I keep identical rows together when splitting data automatically?

Your VBA macro requires a conditional check within its loop. By comparing the target column's value in the current row to the next row (e.g., checking if the bin locations match), you can tell the macro to extend the current batch beyond the hard limit until the bin location changes.

How do I format a specific column as text using VBA?

You can format a column before placing data into it by modifying the NumberFormat property. Use the code snippet: Range("J:J").NumberFormat = "@" to strictly format column J as text.

Excel & Data Problems