FlowerPower
06-19-2023, 08:27 AM
Hi community,
This is my first time on an Excel forum.
I have the following code and it stop at the first line in red, with the run-time error 9.
Sub CopyValueToPrevisionTableDriven()
Dim ws As Worksheet
Dim refTable As ListObject
Dim refRow As ListRow
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim sourceCell As Range
Dim targetCell As Range
Set ws = ThisWorkbook.Worksheets("Data Source")
Set refTable = ws.ListObjects("Copy_A_to_B")
For Each refRow In refTable.ListRows
Set sourceSheet = ThisWorkbook.Worksheets(refRow.Range(1, 1).Value)
Set targetSheet = ThisWorkbook.Worksheets(refRow.Range(1, 2).Value)
Set sourceCell = sourceSheet.Range(refRow.Range(1, 3).Value)
Set targetCell = targetSheet.Range(refRow.Range(1, 4).Value)
targetCell.Value = sourceCell.Value
Application.CutCopyMode = False
Next refRow
End Sub
The run time error 9: Subscript out of range is an error we encounter in VBA when we try to reference something or a variable that does not exist in a code.
All variable are well declared.
I don't know what is the cause ?
This is my first time on an Excel forum.
I have the following code and it stop at the first line in red, with the run-time error 9.
Sub CopyValueToPrevisionTableDriven()
Dim ws As Worksheet
Dim refTable As ListObject
Dim refRow As ListRow
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim sourceCell As Range
Dim targetCell As Range
Set ws = ThisWorkbook.Worksheets("Data Source")
Set refTable = ws.ListObjects("Copy_A_to_B")
For Each refRow In refTable.ListRows
Set sourceSheet = ThisWorkbook.Worksheets(refRow.Range(1, 1).Value)
Set targetSheet = ThisWorkbook.Worksheets(refRow.Range(1, 2).Value)
Set sourceCell = sourceSheet.Range(refRow.Range(1, 3).Value)
Set targetCell = targetSheet.Range(refRow.Range(1, 4).Value)
targetCell.Value = sourceCell.Value
Application.CutCopyMode = False
Next refRow
End Sub
The run time error 9: Subscript out of range is an error we encounter in VBA when we try to reference something or a variable that does not exist in a code.
All variable are well declared.
I don't know what is the cause ?