Method 1 – Directly Setting One Cell Value in Another Sheet
Steps:
- Press Alt + F11 to open the VBA Macro.
- Click on the Insert.
- Select the Module.
- Enter the following code into Module1:
Sub Set_Cell_Value_AnotherSheet()
Dim WS1 As Worksheet, WS2 As Worksheet
'specify sheets to use
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
'set cell value in Sheet2 equal to cell value in Sheet1
WS2.Range("C5").Value = WS1.Range("E5").Value
End Sub
VBA Explanation:
Sub Set_Cell_Value_AnotherSheet()Starting a Subprocedure
Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")In this code block, two variables WS1 and WS2 are declared as type Worksheet. Then, using the Set keyword, these variables are set to reference two specific worksheets in the workbook: “Sheet1” and “Sheet2”.
WS2.Range("C5").Value = WS1.Range("E5").Valueset cell value in Sheet2 equal to cell value in Sheet1
End SubEnding of the Subprocedure
- Save your module and run the code (press F5).
You will see that cell C5 is set in ‘Sheet2’.
Method 2 – Using an Input Box to Set One Cell Value in Another Sheet
Steps:
- Inster the following VBA code in your module without inserting a new one.
- Enter the following code into Module1:
Sub Set_Cell_Value_AnotherSheet_SelectCell()
Dim inputRange As Range
Dim inputVal As Variant
Dim destSheet As Worksheet
' Ask the user to select a cell for input
On Error Resume Next
Set inputRange = Application.InputBox("Select a cell for the input value.", Type:=8)
On Error GoTo 0
If inputRange Is Nothing Then
' User pressed cancel or selected an invalid input, so exit the code.
Exit Sub
Else
' Get the value from the selected input cell
inputVal = inputRange.Value
End If
' Replace "Sheet1" with the name of the source sheet containing the value you want to copy from
With ThisWorkbook.Worksheets("Sheet1")
' Get the destination sheet where you want to copy the value to
Set destSheet = ThisWorkbook.Worksheets("Sheet2") ' Replace "Sheet2" with the name of the destination sheet
destSheet.Range("C5").Value = inputVal
End With
End Sub
VBA Explanation
Sub Set_Cell_Value_AnotherSheet_SelectCell()Beginning of a Subprocedure
Dim inputRange As Range
Dim inputVal As Variant
Dim destSheet As WorksheetThe range object that the user chooses as input will be stored in the Range variable inputRange. The value from the specified input cell will be kept in the inputVal variable of type input. A reference to the worksheet to which the value will be copied will be kept in the destSheet Worksheet type variable.
On Error Resume Next
Set inputRange = Application.InputBox("Select a cell for the input value.", Type:=8)
On Error GoTo 0These lines instruct the user to choose a cell on any workbook sheet by using an InputBox with the Type set to 8. The user may choose a range or a cell to enter as input, according to Type 8. The error is suppressed using On Error Resume Next, and the inputRange is set to Nothing if the user cancels the InputBox or chooses an improper input (for example, several cells or a non-cell region). Once the InputBox has been utilized, the On Error GoTo 0 returns the error handling to the default state.
If inputRange Is Nothing Then
' User pressed cancel or selected an invalid input, so exit the code.
Exit Sub
Else
' Get the value from the selected input cell.
inputVal = inputRange.Value
End IfThese lines determine if the user pushed the cancel button or entered an invalid value if the inputRange variable is set to Nothing. If so, the code uses Exit Sub to end the subroutine. If the inputRange is not Nothing, the user chose a valid cell, and the value from that cell was assigned to the inputVal variable.
With ThisWorkbook.Worksheets("Sheet1")
Set destSheet = ThisWorkbook.Worksheets("Sheet2") destSheet.Range("C5").Value = inputVal
End WithThis code block establishes the context for the “Sheet1” worksheet using a With statement. The destSheet variable is then set to correspond to worksheet “Sheet2.” Cell C5 on “Sheet2” receives the value copied from inputVal.
End SubThis line denotes the ending of the Subprocedure
- Save your module and run the code (press F5).
You will see that cell C5 is set in ‘Sheet2’.
How to Set Multiple Cell Values in Another Worksheet with Excel VBA
Method 1 – Directly Setting Multiple Cells Value in Another Sheet
Steps:
- Enter the following code into Module2:
Sub Set_Cells_Value_AnotherSheet()
Dim WS3 As Worksheet, WS4 As Worksheet
'specify sheets to use
Set WS3 = Sheets("Sheet3")
Set WS4 = Sheets("Sheet4")
'set cell value in Sheet4 equal to cell value in Sheet3
WS4.Range("C5:C14").Value = WS3.Range("E5:E14").Value
End Sub
VBA Explanation
The data in cells C5 to C14 on “Sheet4” is to be copied from cells E5 to E14 on “Sheet3” and pasted there using the VBA code. The variables WS3 and WS4 are used, respectively, to establish references to the source (“Sheet3”) and destination (“Sheet4”) worksheets. The values are then copied from one range to another, effectively from E5:E14 in “Sheet3” to C5:C14 in “Sheet4”.
- Save your module and run (press F5) the code.
You will see that range C5:C14 is set in ‘Sheet4’.
Method 2 – Using Input Box to Set Multiple Cells Value in Another Worksheet with Excel VBA
Steps:
- Enter the following code into your Module2:
Sub Set_Cell_Value_AnotherSheet_SelectCell()
Dim inputRange As Range
Dim inputVal As Variant
Dim destSheet As Worksheet
' Ask the user to select a cell for input
On Error Resume Next
Set inputRange = Application.InputBox("Select a cell for the input value.", Type:=8)
On Error GoTo 0
If inputRange Is Nothing Then
' User pressed cancel or selected an invalid input, so exit the code
Exit Sub
Else
' Get the value from the selected input cell
inputVal = inputRange.Value
End If
' Replace "Sheet3" with the name of the source sheet containing the value you want to copy from
With ThisWorkbook.Worksheets("Sheet3")
' Get the destination sheet where you want to copy the value to
Set destSheet = ThisWorkbook.Worksheets("Sheet4") ' Replace "Sheet4" with the name of the destination sheet
destSheet.Range("C5:C14").Value = inputVal
End With
End Sub
VBA Explanation
The user can manually choose a cell on any worksheet in the workbook and enter it using this VBA code. The code replicates the value from the selected cell to cells C5 through C14 on “Sheet4” if a valid cell is chosen. When a user cancels a selection or enters an erroneous value, the code employs error handling to determine whether to quit the subroutine immediately. The chosen value is then replicated to numerous cells in the target sheet in the provided range, C5:C14 in “Sheet4“.
- Save your module and run the code (press F5).
- Input box window will appear. We selected the range from ‘Sheet3’.
- Click OK.
You will see that range C5:C14 is set in ‘Sheet4’.
Method 3 – Using Cell Reference to Set Cell Value in Another Worksheet with Excel VBA
Steps:
- Enter the following code into Module2:
Sub Cell_Reference()
Dim WS5 As Worksheet, WS6 As Worksheet
'specify sheets to use
Set WS5 = Sheets("Sheet5")
Set WS6 = Sheets("Sheet6")
For i = 5 To 14
WS6.Cells(i, 3).Value = WS5.Cells(i, 3).Value - WS5.Cells(i, 5).Value
Next i
End Sub
VBA Explanation
For each row from 5 to 14 in “Sheet5“, this VBA code will run a calculation, and it will then save the outcome in the corresponding cells in column C of “Sheet6.” It creates references to the source (“Sheet5”) and destination (“Sheet6”) worksheets using the variables WS5 and WS6, respectively. Then, it iterates through rows 5 through 14 using a For loop. It computes the difference between the values in columns E and C of “Sheet5” for each row and enters the result in the corresponding cell in column C of “Sheet6.” This code enables batch operations and the distribution of results between the designated rows in the two sheets.
- Save your module and run the code (press F5).
- We have added column C and column E and sent the summation in column C of ‘Sheet6’.
You will see that range C5:C14 is set in ‘Sheet6’.
How to Set One Cell Value in Another Sheet for a Password Protected Sheet
Steps:
- If your sheet is password protected, you can still set cell values in another worksheet without manually Unprotecting it.
- Insert the Password in the VBA code.
- Enter the following code into Module4:
Sub CopyValueFromSheet9ToSheet10()
Dim wsSheet9 As Worksheet
Dim wsSheet10 As Worksheet
' Set references to the source (Sheet9) and destination (Sheet10) worksheets
Set wsSheet9 = ThisWorkbook.Worksheets("Sheet9")
Set wsSheet10 = ThisWorkbook.Worksheets("Sheet10")
' Unprotect "Sheet9" using the specified password
wsSheet9.unprotect "1234"
' Copy the value from E5 in "Sheet9" to C5 in "Sheet10"
wsSheet10.Range("C5").Value = wsSheet9.Range("E5").Value
' Re-protect "Sheet9" using the same password
wsSheet9.Protect "1234"
End Sub
VBA Explanation
You can copy the value from cell E5 in “Sheet9” and paste it into cell C5 in “Sheet10” using the VBA code. To access the source cell, “Sheet9” is unprotected using the password “1234” before copying. The sheet’s protection is reinstated after the value has been copied and “Sheet9” is re-protected using the same password. This code maintains the sheet protection for “Sheet9” while allowing the secure movement of data between the designated cells in various sheets.
- Save your module and run the code (press F5).
You will see that range C5:C14 is set in ‘Sheet10’.
How to Set Cell Value in Another Worksheet by Copying and Pasting with Excel VBA
Steps:
- Enter the following code into your Module3:
Sub Copy_Example()
Worksheets("Sheet7").Range("E5:E14").Copy Destination:=Worksheets("Sheet8").Range("C5:C14")
End Sub
VBA Explanation
You can use this VBA code to copy the data from cells E5 to E14 in “Sheet7” and paste it into cells C5 to C14 in “Sheet8.” The values in the two worksheets are directly copied, one-to-one, from the chosen source range to the destination range. This code makes it easier to move data across the designated ranges in various sheets in an effective manner, keeping the data accurate and current in both sheets.
- Save your module and run the code (press F5).
You will see that range C5:C14 is set in ‘Sheet8’.
How to Set Cell Value to a Cell on Multiple Sheets Using Excel VBA
Steps:
- Enter the following code into Module5:
Sub Heading_Data_ManualSelection()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim selectedRange As Range
' Set the source sheet where you want to select the headings (B4:E4)
Set wsSource = ThisWorkbook.Worksheets("Sheet11")
' Manually select cells B4 to E4 on the source sheet
On Error Resume Next
Set selectedRange = Application.InputBox("Select cells B4:E4 on " & wsSource.Name, Type:=8)
On Error GoTo 0
If Not selectedRange Is Nothing Then
' Set the destination sheets (Sheet12, Sheet13, and Sheet14)
Set wsDest = ThisWorkbook.Worksheets("Sheet12")
wsDest.Range("B4:E4").Value = selectedRange.Value
Set wsDest = ThisWorkbook.Worksheets("Sheet13")
wsDest.Range("B4:E4").Value = selectedRange.Value
Set wsDest = ThisWorkbook.Worksheets("Sheet14")
wsDest.Range("B4:E4").Value = selectedRange.Value
End If
End Sub
VBA Explanation
The user can manually choose the range from cells B4 to E4 on “Sheet11” using this VBA code, and then copy the selected range to cells B4 to E4 on “Sheet12”, “Sheet13”, and “Sheet14”. It asks the user to choose using an InputBox with the Type property set to 8, allowing the user to choose from numerous cells. The code copies the chosen values to the designated ranges in the destination sheets if the user chooses a valid range. This code offers a flexible and interactive method of moving data between the chosen cells and the specified areas in various sheets.
- Save your module and run the code (press F5).
- After running the code, a window titled Input will appear on your sheet.
- Select your range($B$4:$E$4)
- Press OK.
You will see that the range $B$4:$E$4 is set in ‘Sheet12’, ‘Sheet13’, and ‘Sheet14’.
Things to Remember
Worksheet References: To prevent mistakes when setting cell values with VBA, make sure you correctly reference the target worksheet and cell range.
Error Handling: To handle potential problems that could arise during data processing and ensure smoother execution, incorporate error handling into your VBA code.
Data Validation: To avoid unintended outcomes or errors in your worksheets, validate the data before setting cell values with VBA.
Download the Practice Workbook
You can download the workbook to practice
Related Articles
- How to Get Cell Value as String Using Excel VBA
- How to Get Cell Value by Row and Column in Excel VBA
- Excel VBA: Get Cell Value from Another Workbook without Opening
