Table of Contents
VBA (Visual Basic for Applications) is a programming language used to automate tasks in Microsoft Office applications. To set a cell value in another sheet using VBA, you can use the worksheet object’s range property, which takes the cell address as an argument. You can then assign the desired value to the range object. This allows you to quickly and easily set a cell value in another sheet using VBA.
You can use the following methods in VBA to set the cell value in another sheet:
Method 1: Set One Cell Value in Another Sheet
Sub SetCellAnotherSheet()
Dim wks1 As Worksheet, wks2 As Worksheet
'specify sheets to use
Set wks1 = Sheets("Sheet1")
Set wks2 = Sheets("Sheet2")
'set cell value in Sheet2 equal to cell value in Sheet1
wks2.Range("A2").Value = wks1.Range("A2").Value
End Sub
This particular macro will set the value of cell A2 in Sheet2 to be equal to the value of cell A2 in Sheet1.
Method 2: Set Multiple Cell Values in Another Sheet
Sub SetCellAnotherSheet()
Dim wks1 As Worksheet, wks2 As Worksheet
'specify sheets to use
Set wks1 = Sheets("Sheet1")
Set wks2 = Sheets("Sheet2")
'set cell range in Sheet2 equal to cell range in Sheet1
wks2.Range("A2:A11").Value = wks1.Range("A2:A11").Value
End Sub
This particular macro will set the value of each cell in the range A2:A11 in Sheet2 to be equal to the value of each cell in the range A2:A11 in Sheet1.
The following examples show how to use each method in practice.
Example 1: Set One Cell Value in Another Sheet
Suppose we have the following sheet called Sheet1 that contains the names of various basketball teams:
And suppose we have Sheet2 that contains only a header row:
We can create the following macro to set the value in cell A2 of Sheet2 to be equal to the value in cell A2 of Sheet1:
Sub SetCellAnotherSheet()
Dim wks1 As Worksheet, wks2 As Worksheet
'specify sheets to use
Set wks1 = Sheets("Sheet1")
Set wks2 = Sheets("Sheet2")
'set cell value in Sheet2 equal to cell value in Sheet1
wks2.Range("A2").Value = wks1.Range("A2").Value
End Sub
When we run this macro, we can see that the value in cell A2 of Sheet2 is now set to “Mavs”, which matches the value from cell A2 of Sheet1:
Example 2: Set Multiple Cell Values in Another Sheet
Suppose we would like to set the value of each cell in the range A2:A11 of Sheet2 to be equal to each cell in the range A2:A11 of Sheet1.
We can create the following macro to do so:
Sub SetCellAnotherSheet()
Dim wks1 As Worksheet, wks2 As Worksheet
'specify sheets to use
Set wks1 = Sheets("Sheet1")
Set wks2 = Sheets("Sheet2")
'set cell range in Sheet2 equal to cell range in Sheet1
wks2.Range("A2:A11").Value = wks1.Range("A2:A11").Value
End Sub
When we run this macro, we can see that the values in the range A2:A11 of Sheet2 are now set to be identical to the values in the range A2:A11 of Sheet1:
