Selecting worksheet: Run-time error '9': Subscript out of range

Question

I am extremely new to Excel VBA.

My goal is to create a command button to open a password protected workbook, automatically copy data from selected cells within specific worksheets and paste into the unprotected workbook. The data also needs to be placed in the next empty row as the data in the protected workbook changes daily.

I have not been able to get around a range error at

Worksheets("Sheet14").Select
xDate = Range("L2")

Run-time error '9': Subscript out of range.

Private Sub CommandButton2_Click()
Workbooks.Open Filename:="P:\source.xlsx", Password:="password"
Dim xDate As String
Dim ACD As String
Dim Untouched As String
Dim DailyAct As String
Dim SchedAdherence As String
Dim Status As String
Worksheets("Sheet14").Select
xDate = Range("L2")
Worksheets("Sheet5").Select
ACD = Range("C4")
Worksheets("Sheet5").Select
DailyAct = Range("E4")
Worksheets("Sheet5").Select
SchedAdherence = Range("F4")
Worksheets("Sheet7").Select
Status = Range("B5")

Set TrackingReport = Workbooks("C:\Destination"): TrackingReport.Activate

Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("B1").Select
RowCount = Worksheets("Sheet2").Range("B1").CurrentRegion.Row.Count
With Worksheets("Sheet2").Range("B1")
.Offset(RowCount, 0) = xDate
.Offset(RowCount, 1) = ACD
.Offset(RowCount, 2) = DailyAct
.Offset(RowCount, 3) = SchedAdherence
.Offset(RowCount, 4) = Status
End With
TrackingReport.Save
Set DailyPerformance = Workbooks.Open("P:\source.xlsx",Password:="password")
DailyPerformance.Close
End Sub

Source Data

Destination Data

amy oakes - Run-time error '9': Subscript out of range. Not sure how to identify which line it occurring on as it isn't highlighting any of the code. — Blink21889
To debug press the F8 Key and this will let you step through youre code and the error message will pop up when you get to the line of code that isnt working — AmyOakes
Looks like it is popping up at Worksheets("Sheet14").Select xDate = Range("L2") — Blink21889
Take a look at this might help codevba.com/excel/set_workbook.htm#.WalezrKGOUk — AmyOakes

AmyOakes AmyOakes · Accepted Answer · 2017-09-01T13:57:54

    Private Sub CommandButton2_Click()
    Workbooks.Open Filename:="P:\source.xlsx", Password:="password"
    Dim xDate As String
    Dim ACD As String
    Dim Untouched As String
    Dim DailyAct As String
    Dim SchedAdherence As String
    Dim Status As String
    Worksheets("Sheet14").Select ' Change this sheet name
    xDate = Range("L2")
    Worksheets("Sheet5").Select
    ACD = Range("C4")
    Worksheets("Sheet5").Select
    DailyAct = Range("E4")
    Worksheets("Sheet5").Select
    SchedAdherence = Range("F4")
    Worksheets("Sheet7").Select
    Status = Range("B5")

    Set TrackingReport = Workbooks("C:\Destination"): TrackingReport.Activate ' `CHANGE THIS TO Workbooks.Open Filename:="P:\TrackingReport.xlsx" (or where ever it is saved)` 

    Worksheets("Sheet2").Select
    Worksheets("Sheet2").Range("B1").Select
    RowCount = Worksheets("Sheet2").Range("B1").CurrentRegion.Row.Count ' Here im assuming you are find the last row you to know where to paste from if so i would use something like this instead

    Cells(Rows.Count, 1).End(xlUp).Row ' This looks at your selected column in this case columns 1. Hoowver you would need to ensure that is is a column that is always filled in 

    With Worksheets("Sheet2").Range("B1")
    .Offset(RowCount, 0) = xDate
    .Offset(RowCount, 1) = ACD
    .Offset(RowCount, 2) = DailyAct
    .Offset(RowCount, 3) = SchedAdherence
    .Offset(RowCount, 4) = Status
    End With
    TrackingReport.Save
    Set DailyPerformance = Workbooks.Open("P:\source.xlsx",Password:="password")
    DailyPerformance.Close
    End Sub

In a test workbook i created this worked:

Private Sub CommandButton2_Click()
Dim xDate As String
Dim ACD As String
Dim Untouched As String
Dim DailyAct As String
Dim SchedAdherence As String
Dim Status As String
Dim TrackingReport As Excel.Workbook
Dim DailyPerformance As Excel.Workbook



Set DailyPerformance = Workbooks.Open("P:\source.xlsx", Password:="password")

'Change you sheet names here
Worksheets("Sheet1").Select
xDate = Range("V2").Value
Worksheets("Sheet1").Select
ACD = Range("C4").Value
Worksheets("Sheet1").Select
DailyAct = Range("E4").Value
Worksheets("Sheet1").Select
SchedAdherence = Range("F4").Value
Worksheets("Sheet1").Select
Status = Range("B5").Value

Set TrackingReport = Workbooks.Open("YourPathHere")

Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("B1").Select
RowCount = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("Sheet1").Range("B1")
.Offset(RowCount, 0) = xDate
.Offset(RowCount, 1) = ACD
.Offset(RowCount, 2) = DailyAct
.Offset(RowCount, 3) = SchedAdherence
.Offset(RowCount, 4) = Status
End With
TrackingReport.Save


DailyPerformance.Close
End Sub

Selecting worksheet: Run-time error '9': Subscript out of range

1 Answers