WPS Spreadsheet VBA: Repeat a Cell Value a Specific Number of Times
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.
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.
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.
Open the workbook in WPS Spreadsheet, select the Developer tab, and choose Visual Basic. Insert a standard module through Insert > Module.
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
In the Set wsSource and Set wsDestination lines, replace SourceSheet and DestinationSheet with the exact tab names in your workbook, including spaces and punctuation.
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.
Use a worksheet formula when VBA is unavailable
Use this alternative for a simple repeated list when macros are disabled or the workbook only needs a formula-based result.
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. Open the workbook: Launch WPS Office and open the spreadsheet containing the source and destination worksheets.
- 2. Open Visual Basic: Select Developer > Visual Basic, then choose Insert > Module to create a standard VBA module.
- 3. Paste and customize the macro: Paste the PopulateNames macro and replace SourceSheet and DestinationSheet with your actual worksheet names.
- 4. Run the automation: Select Run > Run Sub/UserForm in the VBA editor and verify the repeated values on the destination worksheet.

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.




