Runtime Error 9, Subscript Out of Range - Trying to copy information to another sheet

Question

I am in the middle of tidying up an old piece of code. I've managed to wiggle out a few errors but this one has me flummoxed.

Despite all of the first half of the code working and allowing me to copy the information in their cells to another page consisting of a data table - using the same method to move information from sheet 8 to the 3rd page isn't working.

If I remove Sheets(8).Range("A1").Value and replace it with A1, that line runs, so I assume it is a problem within that?

I think there may be a couple errors below this point too in which I'll ask (as once again Error 9 has turned up).

Here is the code below, the erroneous line is:

.Range("B" & RowToPasteTo).Value = Sheets("Config").Range("A1").Value

Sub PostToPipeline()
    With ThisWorkbook.Sheets(3)
        Dim RowToPasteTo As Long
        RowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

        'This defines the variable RowToPasteTo as the next empty line on the Pipeline

        .Range("D" & RowToPasteTo).Value = Sheets(2).Range("C4").Value 'This prints the clients name to pipeline.With the left of the '=' being the destination, and the right being the source of information.
        .Range("E" & RowToPasteTo).Value = Sheets(2).Range("C5").Value 'This prints TAS Principal
        .Range("F" & RowToPasteTo).Value = Sheets(2).Range("C6").Value 'This prints Pillar

        .Range("C" & RowToPasteTo).Value = Date 
        .Range("J" & RowToPasteTo).Value = Date 

        ThisWorkbook.Sheets(8).Range("A1").Value = ThisWorkbook.Sheets(8).Range("A1").Value + 1 'Sheet makes a permanent variable for reference and adds one with each use.

        .Range("B" & RowToPasteTo).Value = Sheets(8).Range("A1").Value

        Sheets(1).Range("C4:C11").Select.ClearContents 'Clears the information held in input table.
        Range("A1").Select

        Sheets(3).Range("A1").Select 'Deselects range
    End With
End Sub

What I want is for the value of Cell A1 in Sheet 8 ("Config") to be posted to the next empty cell in column B.

Please [avoid using both Select and Activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Tim Stack, May 21 '19 at 09:07
And refer to the workbook and worksheet with each range object. For example, `Sheet(8).Range(...)` would refer to the active workbook. This may not be the workbook you want VBA to look at, and this wb may not even have 8 sheets, resulting in an out of range error — Tim Stack, May 21 '19 at 09:08
This workbook definitely ha a sheet 8 - I called upon the index number to ensure it would still go to the right place even with a name change. — Breakingfaith, May 21 '19 at 09:13
You're not using `Activate`, but you are using `Select`. The linked question covers both — Tim Stack, May 21 '19 at 09:14
In that case, use `ThisWorkbook.Sheets(8)`. You're not specifying the workbook so VBA has to assume a workbook. Even better would be to use the code name, this never changes, unlike the sheet index (position) or name. — Tim Stack, May 21 '19 at 09:14
it's idea to refer to each worksheets with it's name and not the index. — Error 1004, May 21 '19 at 09:16
@Mimi you say (in a comment) _I called upon the index number to ensure it would still go to the right place even with a name change_. If you are worried the user might change a sheets' name, you should _also_ worry a user might _move_ a sheet. If they do, then `Sheets(8)` _will_ refer to the wrong sheet. — chris neilsen, May 21 '19 at 09:45

score 1 · Accepted Answer · answered May 21 '19 at 09:20

You could make the changes and try the code. if you have any issues please let me know

Option Explicit

Sub PostToPipeline()

    Dim RowToPasteTo As Long
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws8 As Worksheet

    With ThisWorkbook
        Set ws1 = .Sheets(1) 'Change the number in the bracket with the name of the worksheet ( e.g. .Sheets("Sheet1"))
        Set ws2 = .Sheets(2) 'Change the number in the bracket with the name of the worksheet ( e.g. .Sheets("Sheet1"))
        Set ws3 = .Sheets(3) 'Change the number in the bracket with the name of the worksheet ( e.g. .Sheets("Sheet1"))
        Set ws8 = .Sheets(8) 'Change the number in the bracket with the name of the worksheet ( e.g. .Sheets("Sheet1"))
    End With

    With ws3

        RowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

        'This defines the variable RowToPasteTo as the next empty line on the Pipeline

        .Range("D" & RowToPasteTo).Value = ws2.Range("C4").Value 'This prints the clients name to pipeline.With the left of the '=' being the destination, and the right being the source of information.
        .Range("E" & RowToPasteTo).Value = ws2.Range("C5").Value 'This prints TAS Principal
        .Range("F" & RowToPasteTo).Value = ws2.Range("C6").Value 'This prints Pillar

        .Range("C" & RowToPasteTo).Value = Date
        .Range("J" & RowToPasteTo).Value = Date

        .Range("B" & RowToPasteTo).Value = ws8.Range("A1").Value

        .Range("A1").Select 'Deselects range

    End With

    ws8.Range("A1").Value = ws8.Range("A1").Value + 1 'Sheet makes a permanent variable for reference and adds one with each use.
    ws1.Range("C4:C11").ClearContents 'Clears the information held in input table.

End Sub

Hi I've just tried this out - I am a little concern as it has removed all of my data validation and formatting from these cells.... Not sure how though as all I changed was your clearcontents line, and when I did that with select before it has just remvoed text — Breakingfaith, May 21 '19 at 09:26
By the way! Just implemented it in to a bit of mine - it's perfect thank you so much! — Breakingfaith, May 21 '19 at 09:43
P.s. yeah I saw that after I commented. Not sure what took it out - something I did for sure. Thank you though — Breakingfaith, May 21 '19 at 09:45
Glad to help again. i that answer helps you you could vote it to help others with the same issue. — Error 1004, May 21 '19 at 10:48

Runtime Error 9, Subscript Out of Range - Trying to copy information to another sheet

1 Answers1