Copy Cell Value To Defined Sheet & Cell Reference (Loop)

Question

I'm trying to copy values from a range based on a user defined sheet and cell reference. For example in A1 I have defined the Sheet Name to be copied to, B1 is the cell reference to be copied to & C1 is the value to be copied. The below code completes this for row 1 only, but I require to loop this for all rows in a defined range (i.e. named range A1:C200) or until the row is blank.

Preferably I would have an option to copy the cell value in the range (e.g. C1 as above) or the formula that exists in the range.

Sub CopyValues()

Dim SheetName
Dim CellRef
Dim Value

With ThisWorkbook.Sheets("Sheet1")
SheetName = Range("A1").Value
CellRef = Range("B1").Value
Value = Range("C1").Value
End With

ThisWorkbook.Sheets(SheetName).Range(CellRef).Value = Value

End Sub

Answer 1

You were almost there

Sub CopyValues()

Dim SheetName
Dim CellRef
Dim Value

With ThisWorkbook.Sheets("Sheet1")
    For i = 1 to 200
        SheetName = Range("A" & i).Value
        CellRef = Range("B" & i).Value
        Value = Range("C" & i).Value
        ThisWorkbook.Sheets(SheetName).Range(CellRef).Value = Value
    Next i
End With

End Sub

Answer 2

Sub CopyValues()
    With ThisWorkbook.WorkSheets("Sheet1")
        For Each cell in .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
            ThisWorkbook.Sheets(cell.Value).Range(cell.Offset(,1).Formula = cell.Offset(,2).Formula
        Next
    End With
End Sub

Answer 3

Thank you both! I settled on the below which solved my problem.

Sub CopyValues()

Dim SheetName
Dim CellRef
Dim Value

With ThisWorkbook.Sheets("Sheet1")
    For i = Range("SheetNameRange").Row To Range("SheetNameRange").Row + Range("SheetNameRange").Rows.Count - 1
        SheetName = Range("A" & i).Value
        CellRef = Range("B" & i).Value
        Value = Range("C" & i).Value
        ThisWorkbook.Sheets(SheetName).Range(CellRef).Value = Value
    Next i
End With

End Sub