VBA & Macro Problems

WPS Spreadsheet VBA: Repeat a Cell Value a Specific Number of Times

John WilsonJohn Wilson Nov 12, 2026 870 views

Question details

The user needs to repeat a value from one worksheet a specified number of times on another worksheet.

Product
WPS Spreadsheet VBA
Device & OS
not provided
Scenario
A name is stored in cell A4 and the repetition count is stored in B4 on a source worksheet. The macro should write the name repeatedly on a destination worksheet.
Observed behavior
The required automation is a VBA loop that reads the source cells and populates the destination worksheet.
Before you start

Confirm the exact source and destination worksheet names, and make sure A4 contains the value to repeat while B4 contains a nonnegative whole number. Save the workbook in a macro-enabled format if your WPS Office edition requires it.

Solution 1Recommended

Run a VBA macro that repeats the source value on a destination sheet

Use this solution when the value and repetition count are stored in fixed cells and the result should start in cell A1 of another worksheet.

The macro reads the value from A4 and the count from B4, clears the destination worksheet, validates the count, and writes the value into column A for the requested number of rows. Replace the example worksheet names before running it.

1
Open the VBA editor

Open the workbook in WPS Spreadsheet, select the Developer tab, and choose Visual Basic. Insert a standard module through Insert > Module.

2
Add the macro

Paste the following code into the standard module: Sub PopulateNames() Dim wsSource As Worksheet Dim wsDestination As Worksheet Dim nameToPopulate As String Dim countValue As Variant Dim countToPopulate As Long Dim i As Long Set wsSource = ThisWorkbook.Worksheets("SourceSheet") Set wsDestination = ThisWorkbook.Worksheets("DestinationSheet") nameToPopulate = CStr(wsSource.Range("A4").Value) countValue = wsSource.Range("B4").Value If Not IsNumeric(countValue) Then MsgBox "Cell B4 must contain a nonnegative whole number." Exit Sub End If If CDbl(countValue) < 0 Or CDbl(countValue) <> Int(CDbl(countValue)) Then MsgBox "Cell B4 must contain a nonnegative whole number." Exit Sub End If countToPopulate = CLng(countValue) wsDestination.Cells.Clear For i = 1 To countToPopulate wsDestination.Cells(i, 1).Value = nameToPopulate Next i End Sub

3
Update the worksheet names

In the Set wsSource and Set wsDestination lines, replace SourceSheet and DestinationSheet with the exact tab names in your workbook, including spaces and punctuation.

4
Run the macro

Return to the VBA editor, place the cursor inside PopulateNames, and select Run > Run Sub/UserForm, or press F5. The repeated value will be written to cells A1 through A[count] on the destination worksheet.

Result: The destination sheet is cleared before the new values are written. Remove or modify wsDestination.Cells.Clear if existing destination data must be preserved.

Automate Repeated Values in WPS Spreadsheet with VBA

WPS Spreadsheet can run a VBA macro that reads a source value and count, validates the input, clears the destination range, and fills the requested number of rows. You can also use a formula when macros are unavailable.

  1. 1. Open the workbook: Launch WPS Office and open the spreadsheet containing the source and destination worksheets.
  2. 2. Open Visual Basic: Select Developer > Visual Basic, then choose Insert > Module to create a standard VBA module.
  3. 3. Paste and customize the macro: Paste the PopulateNames macro and replace SourceSheet and DestinationSheet with your actual worksheet names.
  4. 4. Run the automation: Select Run > Run Sub/UserForm in the VBA editor and verify the repeated values on the destination worksheet.
Automates repeated-value lists from fixed source cellsSupports VBA macros and formula-based alternativesProvides a familiar spreadsheet interface for editing and testing the resultCompatible with Microsoft Excel .XLSM and .XLSX workbook formats
microsoft office alternative - wps office

Frequently Asked Questions

Where should the name and repetition count be entered?

Enter the value to repeat in cell A4 and a nonnegative whole number in cell B4 on the source worksheet referenced by the macro.

Why does the macro show a worksheet subscript error?

The worksheet names in Set wsSource and Set wsDestination must exactly match the visible worksheet tabs. Replace SourceSheet and DestinationSheet with the correct names.

Where does the repeated value appear?

The macro writes the value into column A of the destination worksheet, starting at A1 and continuing through the number of rows specified in B4.

Will the macro delete existing destination data?

Yes. The wsDestination.Cells.Clear line clears the destination worksheet before populating it. Remove that line or replace it with a specific range if existing content must remain.

What happens if B4 contains text, a decimal, or a negative number?

The validation displays a message and stops the macro when B4 is not numeric, is negative, or is not a whole number.