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:
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Find Last Used Row
VBA: How to Count Number of Rows in Range
VBA: How to Count Number of Used Columns
how update select cells for example we have list of customer and want to change information of select customer
Zach…this great can you tell me how to dynamically change the source sheet? I am using the macro to create a summary line on the target sheet each time I set up a new detail tab. Automating the whole process from creating the tab to creating summary line. Detail tab name is the same as the last entry in a column on the target sheet.
Hi Paul…You can dynamically change the source sheet by using VBA to refer to the last entry in a specific column on your target sheet. Below is a VBA macro that automates the entire process:
1. **Creates a new detail tab based on the last entry in a column on the target sheet.**
2. **Sets a summary line in the target sheet referencing the new detail tab.**
### VBA Code:
“`vba
Sub CreateDetailTabAndSummary()
Dim wsTarget As Worksheet
Dim wsDetail As Worksheet
Dim detailSheetName As String
Dim lastRow As Long
Dim newSheet As Worksheet
‘ Set the target sheet (where summary is created)
Set wsTarget = ThisWorkbook.Sheets(“Summary”) ‘ Change “Summary” to your actual target sheet name
‘ Find the last entry in Column A (adjust as needed)
lastRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row
detailSheetName = wsTarget.Cells(lastRow, 1).Value
‘ Check if a sheet with the name already exists
On Error Resume Next
Set wsDetail = ThisWorkbook.Sheets(detailSheetName)
On Error GoTo 0
‘ Create the detail sheet if it does not exist
If wsDetail Is Nothing Then
Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
newSheet.Name = detailSheetName
Else
MsgBox “Sheet ‘” & detailSheetName & “‘ already exists!”, vbExclamation
Exit Sub
End If
‘ Add summary line in the target sheet (modify column as needed)
wsTarget.Cells(lastRow, 2).Value = “Summary for ” & detailSheetName
wsTarget.Cells(lastRow, 3).Formula = “=” & detailSheetName & “!A1” ‘ Example: Pulling a value from A1 of the new detail sheet
MsgBox “Detail tab ‘” & detailSheetName & “‘ created and summary updated!”, vbInformation
End Sub
“`
### How It Works:
1. **Finds the last non-empty row in Column A of the target summary sheet.**
2. **Uses the value from that row as the new sheet name.**
3. **Checks if the sheet already exists to avoid duplication.**
4. **Creates a new sheet with that name if it doesn’t exist.**
5. **Adds a summary row in the target sheet, linking it to the newly created detail sheet.**
### Customization:
– Change `”Summary”` to the actual name of your target sheet.
– Modify `Column A` if your sheet names are stored in another column.
– Modify `wsTarget.Cells(lastRow, 3).Formula` to reference different data from the new sheet.