Assign a specific value to a cell from a range depending on sheet name
I am trying to assign a different number from a range to the same cell in different sheets.
For example, I have range c4:c9. In Sheet A, A1=c4; Sheet B, A1=c5; Sheet C, A1=c6; etc.
I cannot make my macro move down the range.
Instead for each sheet it assigns C4 to A1.
I currently have the sheets named in a range so VBA will know to move sheets.
Here is my code:
Sub update() Dim sheet_name As Range For Each sheet_name In Sheets("Home").Range("A4:A9") If sheet_name.Value = "" Then Exit For Else Sheets(sheet_name.Value).Select Range("A1").Select ActiveCell.Formula = "=Home!rc" Range("A2").Select End If Next sheet_name End Sub
sheet_name is not a range. While the strings inside the cells may be sheet names, they aren't sheets.
Sub test() Dim sSheetName As String For Each cell In [sheetnames] sSheetName = cell.Value Sheets(sSheetName).Range("a1") = "some formula" Next cell End Sub