In this blog post, we’ll look at how to do it, how important it is for improving the handling of data, and offer useful examples of how to set cell values across worksheets using VBA. Setting the value of a cell in one worksheet from another worksheet is a usual process in Excel VBA. After reading the article “Excel VBA Set Cell Value in Another Worksheet”, you’ll be able to use a variety of different options for setting cell values, enabling you to easily manage your data activities across many sheets.
We’ll begin with setting the value of a single cell in a different worksheet, then move on to more complex methods like setting multiple cell values in several worksheets. We’ll also examine how to deal with sheets that have password protection and how to copy and paste cell values between sheets.
How to Set One Cell Value in Another Worksheet with Excel VBA
1. Directly Setting One Cell Value in Another Sheet
Step 1:
- First of all, press Alt + F11 to open the VBA Macro.
- Click on the Insert.
- Select the Module.
Step 2:
- Copy the following code and paste it 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
Step 3:
- Save your module and run the code (press F5).
- Therefore, you will see that cell C5 is set in ‘Sheet2’.
2. Using an Input Box to Set One Cell Value in Another Sheet
Step 1:
- You can insert the following VBA code in your module without inserting a new one.
- Copy the following code and paste it 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. The user chose a valid cell, and the value from that cell was assigned to the inputVal variable if the inputRange is not Nothing.
With ThisWorkbook.Worksheets("Sheet1")
Set destSheet = ThisWorkbook.Worksheets("Sheet2") destSheet.Range("C5").Value = inputVal
End WithThe context for the “Sheet1” worksheet is established in this code block 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
Step 2:
- Save your module and run the code (press F5).
- As a result, you will see that cell C5 is set in ‘Sheet2’.
How to Set Multiple Cell Values in Another Worksheet with Excel VBA
1. Directly Setting Multiple Cells Value in Another Sheet
Step 1:
- In this section, we will demonstrate setting multiple cell values in Another Worksheet with Excel VBA contrary to the previous section where we displayed the process of Setting One Cell Value in Another Worksheet with Excel VBA
- Copy the following code and paste it 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”.
Step 2:
- Save your module and run (press F5) the code.
- Finally, you will see that range C5:C14 is set in ‘Sheet4’.
2. Using Input Box to Set Multiple Cells Value in Another Worksheet with Excel VBA
Step 1:
- We can set multiple cell values in Another Worksheet with Excel VBA by using Input Box.
- Copy the following code and paste it 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“.
Step 2:
- Save your module and run the code (press F5).
- Input box window will appear. We selected the range from ‘Sheet3’ >> clicked OK.
- Finally, you will see that range C5:C14 is set in ‘Sheet4’.
3. Using Cell Reference to Set Cell Value in Another Worksheet with Excel VBA
Step 1:
- In this method, we will do something unlike previous methods. Before Setting Multiple Cell Values in Another Worksheet with Excel VBA, we will make simple calculations like addition and send the summation to another sheet.
- Copy the following code and paste it 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.
Step 2:
- 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’.
- Finally, 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
Step 1:
- If your sheet is password protected, you can still set cell values in another worksheet without manually Unprotecting it.
- But you have to insert the Password in the VBA code.
- Copy the following code and paste it 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.
Step 2:
- Save your module and run the code (press F5).
- Finally, 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
Step 1:
- We can fulfill our purpose of setting cell values in another worksheet with a similar result, but this time by using copy and paste in VBA.
- So, copy the following code and paste it 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.
Step 2:
- Save your module and run the code (press F5).
- Finally, 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
Step 1:
- In the last section, we are going to discuss a method where we select a range and send that range to multiple sheets.
- Copy the following code and paste it 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.
Step 2:
- 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.
- Finally, 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.
Frequently Asked Questions (FAQs)
Q1. Can I set cell values in different workbooks using VBA?
Answer: As long as both workbooks are open and available, it is possible to set cell values between workbooks using VBA. Both the filename and the workbook index refer to the target workbook.
Q2. Is it possible to use VBA to set multiple cell values at once?
Answer: Yes, you can use VBA to set multiple cell values at once. Set the target range parameters and give them an array of values. By doing so, you can update numerous cells using a single VBA statement.
Q3. Can I use VBA to set cell values in worksheets that are hidden?
Answer: Yes, VBA enables you to change cell values in worksheets that are hidden without needing to unhide the sheet. Both visible and hidden worksheets can have their cell values accessed and changed programmatically.
Q4. Can VBA be used to set cell values according to criteria or conditions?
Answer: Yes, VBA is very versatile and can set cell values in accordance with particular requirements or criteria. For example, If…Then…Else, and loops (such as For…Next) create conditional statements and control the dynamic setting of cell values.
Download Practice Workbook
You can download the practice workbook from the following download button.
Conclusion
Finally, you have learned a lot about using Excel VBA to set cell value in another worksheet. With the help of these techniques, you can manage your data effectively and improve your Excel abilities. I recommend using the practice book to further your understanding and apply what you’ve learned to your datasets. For Excel aficionados like you, we continue to make content because of your support. Contact us at any time if you have questions or comments. The Exceldemy team will be supporting you as you learn. Keep in touch with us as we continue to offer useful resources to assist you in thriving.
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
- Excel VBA: Read Cell Value into Variable
