VBA sub - result in run-time error 9, but code looks good

[FlowerPower]

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 ?

[Aflatoon]

It means that the workbook with the code in it does not have a sheet with the name specified in that row of the table.

[FlowerPower]

It means that the workbook with the code in it does not have a sheet with the name specified in that row of the table.

[FlowerPower]

In the syntax of line: ThisWorkbook.Worksheets(refRow.Range(1, 1).Value)
Where would the sheet name shown. The sheet is "Data Source" and is refered by the variable "ws".
Set ws = ThisWorkbook.Worksheets("Data Source")
https://filestore.community.support.microsoft.com/api/images/50b72c4c-bc3c-466b-ac2c-ffa706fd36ee?upload=true&fud_access=rhYinQu9k%2fQ7lDpdCsGkl3eusmFMeQelfHau4vFjY8lur4WJxfiEK7M3p4%2bs 9FkBPy4gjPk1BpzKedTB0Zjh%2bzCRtDd6jCa%2fyokBDwISGOaLz2NNyurtbpNCCvuSAxDElHG %2fn6DLSEvx6me%2bhAe45JDIUNKxIOEEd20AGDGRPHG%2ben0EVNUpAVqcmd0VV7faZ9nNjNhP 9PPig%2bzzmDgTRoelvmmrrRbHNCzWA6K0ylcnqwbvTwurqasxdyUukUub4Nsjk6a5NRa8%2fK5 STJwkILRHozgJJjs9Z5XXvGGGZdwPgiQPWWSdwlqGjCTgW8dBK0nZl%2bcpObTL2QHRqJ6N8%2f hN6mlOzSSnVQqfmSX2dFA%3d (https://filestore.community.support.microsoft.com/api/images/50b72c4c-bc3c-466b-ac2c-ffa706fd36ee?upload=true&fud_access=rhYinQu9k%2fQ7lDpdCsGkl3eusmFMeQelfHau4vFjY8lur4WJxfiEK7M3p4%2bs 9FkBPy4gjPk1BpzKedTB0Zjh%2bzCRtDd6jCa%2fyokBDwISGOaLz2NNyurtbpNCCvuSAxDElHG %2fn6DLSEvx6me%2bhAe45JDIUNKxIOEEd20AGDGRPHG%2ben0EVNUpAVqcmd0VV7faZ9nNjNhP 9PPig%2bzzmDgTRoelvmmrrRbHNCzWA6K0ylcnqwbvTwurqasxdyUukUub4Nsjk6a5NRa8%2fK5 STJwkILRHozgJJjs9Z5XXvGGGZdwPgiQPWWSdwlqGjCTgW8dBK0nZl%2bcpObTL2QHRqJ6N8%2f hN6mlOzSSnVQqfmSX2dFA%3d)

[FlowerPower]

I found my mistake, the sheet "Selection" was called "Sélection" in the table (with a french accent).
Also the sheet "Prevision de la semaine" was called "Prévision de la semaine" in the table, again with a french accent.

Now that I correct that, it works. Thanks Aflatoon and others that took a look at it.

[Aussiebear]

Perhaps in future it may be better to name objects with something other than key word in Excel. Also if this issue is resolved can you go to "Thread Tools" and use the Mark this thread as Solved please?

[soxsgub555]

Thanks for letting us know some good tips

Mapquest (https://mapquest-directions.io)