Use a Dynamic Sheet Name in an Excel VBA Macro
Question details
Use a dynamically generated worksheet name to place a value in cell A1 of the matching worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A sheet number and text are entered on a control sheet, and the text needs to be written to the corresponding Adder worksheet.
- Observed behavior
- An ordinary IF formula cannot write a value into a cell on another worksheet.
Confirm that the target sheets follow the names Adder 1 and Adder (2), Adder (3), and so on. Identify the sheet containing the sheet number and text, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
Write to the matching worksheet with VBA
Use this macro when a sheet number determines which worksheet receives the text in cell A1.
The macro reads B1 and C1 from a worksheet named Control. Change that name and the cell references if your workbook uses different locations.
In Excel, open the workbook, then select Developer > Visual Basic. If Developer is hidden, enable it under File > Options > Customize Ribbon.
Select Insert > Module and paste this code: Sub UpdateAdderSheet() Dim controlSheet As Worksheet Dim targetSheet As Worksheet Dim sheetNumber As Long Dim targetName As String Set controlSheet = ThisWorkbook.Worksheets("Control") If Not IsNumeric(controlSheet.Range("B1").Value) Then MsgBox "Enter a sheet number in B1." Exit Sub End If sheetNumber = CLng(controlSheet.Range("B1").Value) If sheetNumber < 1 Then MsgBox "Enter a positive sheet number in B1." Exit Sub End If If sheetNumber = 1 Then targetName = "Adder 1" Else targetName = "Adder (" & sheetNumber & ")" End If On Error Resume Next Set targetSheet = ThisWorkbook.Worksheets(targetName) On Error GoTo 0 If targetSheet Is Nothing Then MsgBox "Worksheet not found: " & targetName Exit Sub End If targetSheet.Range("A1").Value = controlSheet.Range("C1").Value End Sub
On the Control sheet, enter a whole sheet number in B1 and the text to write in C1. For example, enter 2 to target Adder (2).
In Excel, select Developer > Macros, choose UpdateAdderSheet, and select Run. Check cell A1 on the matching Adder sheet.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move everyday workbook tasks between suites. It does not fix Excel's VBA behavior, and macro support and compatibility depend on the WPS version and available VBA components.

Frequently Asked Questions
Can an Excel IF formula write into another worksheet?
No. A formula returns a result in the cell containing it; it cannot write a value into another cell. Use VBA to write to the target sheet, or put a formula in the target cell that reads the source.
What happens if the target sheet does not exist?
The macro displays a message with the missing worksheet name and stops. Check the sheet tab name and the number in Control!B1.
How do I change the cells used by the macro?
In Developer > Visual Basic, edit the module: replace B1 with the sheet-number cell, C1 with the text cell, and A1 with the destination cell. Also replace Control if the source worksheet has a different name.
Will the macro run automatically when I enter text?
No. Run UpdateAdderSheet from Developer > Macros after entering the values. Automatic updates require a worksheet event macro.




