VBA & Macro Problems

Use VBA to Fill Blank Cells with Names in Excel Roster

Adam Davis Oct 9, 2026 869 views

Question details

The user needs a VBA macro to automatically copy staff names from a source worksheet and fill blank cells in a target roster, assigning them into specific groups.

How to Use VBA to Fill Blank Cells with Names in an Excel Roster

Product: Excel
Device & OS: not provided
Scenario: Automating staff and backup assignments in an Excel roster to prevent duplicate entries and save time compared to manual copy-pasting.
Observed behavior: Staff names need to be copied and dynamically arranged into grouped cells (e.g., blocks of four) until the original source list is fully processed and exhausted.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Excel or WPS Spreadsheet security settings before running the code.

Solution 1Recommended

Implement a VBA Macro to Fill Roster Cells

Use a custom VBA script utilizing the Offset and Resize properties to dynamically copy staff names from a source sheet and allocate them into grouped cells in your target roster.

This VBA procedure defines the source and target ranges, copies names using the Offset function, and fills the roster in defined blocks. It loops through the source list and advances automatically until an empty cell is reached, removing the need for manual data entry.

Below is the VBA snippet used to perform this task: Sub FillRoster() Dim src As Range Dim trg As Range Application.ScreenUpdating = False Set src = Worksheets("Sheet2").Range("A1") Set trg = Worksheets("Sheet1").Range("B1") trg.Offset(0, 2).Value = src.Offset(4).Value trg.Offset(0, 3).Value = src.Offset(5).Value Do trg.Value = src.Value trg.Offset(0, 1).Value = src.Offset(1).Value trg.Offset(1).Value = src.Offset(2).Value trg.Offset(1, 1).Value = src.Offset(3).Value trg.Offset(1, 2).Resize(2, 2).Value = trg.Resize(2, 2).Value Set src = src.Offset(4) Set trg = trg.Offset(2) Loop Until src.Value = "" Application.ScreenUpdating = True End Sub

Open the VBA Editor

Open your workbook and press ALT + F11 on your keyboard to launch the VBA Editor.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank workspace for your macro.

Paste the VBA Code

Copy the provided FillRoster script and paste it into the new module window. Ensure that 'Sheet2' accurately reflects your source sheet and 'Sheet1' reflects your target roster.

Run the Macro

Press F5 or click 'Run > Run Sub/UserForm' from the top toolbar to execute the macro and populate your blank roster cells.

Implement a VBA Macro to Fill Roster Cells

Customizing the Script: You can modify the Resize and Offset parameters within the Do Loop to accommodate larger staff groups or different backup assignment rules based on your specific roster layout.

Advanced Spreadsheet Automation

Manage Rosters and Run VBA Macros seamlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful support for VBA and macros, allowing you to easily automate repetitive data entry tasks like roster assignments. Write, edit, and execute your scripts in a highly compatible environment.

1. Open your File in WPS: Download and install WPS Office, then open your .xlsm roster file in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon menu and click on 'VBA Editor'.
3. Execute the Code: Paste your roster-filling VBA code into a module and click 'Run' to instantly populate your spreadsheet.

Fully compatible with Microsoft Excel macro formats (.xlsm).Built-in VBA editor for writing, editing, and debugging automation scripts.Free, lightweight, and fast alternative for complex data management.Familiar user interface ensuring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro not running in the spreadsheet?

Macros may be disabled by your security settings. Navigate to the Developer tab, click Macro Security, and enable macros. Additionally, ensure your document is saved as a Macro-Enabled Workbook (.xlsm).

How do I change the number of staff assigned per group?

You can adjust the group size by modifying the Resize and Offset parameters inside the Do Loop of the VBA script. For example, updating Resize(2, 2) to Resize(3, 3) alters the dimensions of the target block.

Can I run this macro across differently named worksheets?

Yes, you must update the Worksheets("Sheet2") and Worksheets("Sheet1") references in the VBA code to exactly match the tab names of your source and target worksheets.

Excel & Data Problems