VBA & Macro Problems

Use a Dynamic Sheet Name in an Excel VBA Macro

Khadija KhanKhadija Khan Nov 18, 2026 868 views

Question details

Use a dynamically generated worksheet name to place a value in cell A1 of the matching worksheet.

Use a Dynamic Sheet Name in an Excel VBA Macro
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.
Before you start

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).

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook, then select Developer > Visual Basic. If Developer is hidden, enable it under File > Options > Customize Ribbon.

2
Insert the macro

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

3
Enter the source values

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).

4
Run the macro

In Excel, select Developer > Macros, choose UpdateAdderSheet, and select Run. Check cell A1 on the matching Adder sheet.

Write to the matching worksheet with VBA
Running the macro updates A1: The macro replaces any existing value or formula in the target sheet's A1. It runs when selected from the Macros dialog; it does not run automatically when B1 or C1 changes.
Free Microsoft Office alternative

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.

Open and edit Excel XLSX spreadsheets in WPS SpreadsheetsUse a familiar spreadsheet interface for worksheet and cell editingKeep working with sheet-based data in a lightweight office suiteMove everyday spreadsheet work between Excel and WPS Office
microsoft office alternative - wps office

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.