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 useSet 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 useSet 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 useSet 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 useSet 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: