CODEWITHSUNDEEP
vbaexcel
pottolom
pottolom

Reputation: 289

Run-time error '9' if I open another workbook

I have a workbook in which I am running some VBA code successfully. However, if I leave this workbook open in the background and then open a second workbook to work on something else, the code in first workbook stops running and I get a "Run-time error '9': Subscript out of range" message.

Does anyone know why this might be occurring? I am a real novice (I only began delving into VBA this week!) but suspect it might be because my code perhaps refers to the 'active' workbook and is therefore now trying to run on the second workbook I opened. This is the main 'module' code I am using:

Sub Auto_Open()

Call CopyValues

End Sub

    Sub CopyValues()

    Dim RowNo As Long
RowNo = Sheets(4).Cells(Rows.Count, 4).End(xlUp).Row + 1

    Sheets(4).Cells(RowNo, 2) = Sheets(1).Cells(14, 2)
    Sheets(4).Cells(RowNo, 3) = Sheets(1).Cells(14, 3)
    Sheets(4).Cells(RowNo, 4) = Sheets(1).Cells(14, 4)
    Sheets(4).Cells(RowNo, 5) = Sheets(1).Cells(15, 2)
    Sheets(4).Cells(RowNo, 6) = Sheets(1).Cells(15, 3)
    Sheets(4).Cells(RowNo, 7) = Sheets(1).Cells(15, 4)
    Sheets(4).Cells(RowNo, 8) = Sheets(1).Cells(16, 2)
    Sheets(4).Cells(RowNo, 9) = Sheets(1).Cells(16, 3)
    Sheets(4).Cells(RowNo, 10) = Sheets(1).Cells(16, 4)
    Sheets(4).Cells(RowNo, 11) = Sheets(1).Cells(17, 2)

If RowNo > 1440 Then
     Rows("2:2").Delete Shift:=xlUp
End If

Application.OnTime Now + TimeValue("00:01:00"), "CopyValues"

End Sub

I am also running this on the workbook:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim z As Integer
    z = 1440

    Worksheets("Sheet4").Range("A2:K" & z).Delete Shift:=xlUp
End Sub

And this on 'Sheet4':

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Or Target.Column > 11 Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, 1) = Now
    Application.EnableEvents = True
End Sub

Upvotes: 0

Views: 140

Answers (2)

Tom
Tom

Reputation: 9878

Have a look at this. You will need to check your sheet names in the VBA Editor match. Doing it this way will ensure that you're targeting the correct sheet (even if someone changes the sheet name in the front end)

Sub Auto_Open()
    Call CopyValues
End Sub

Sub CopyValues()
    Dim RowNo As Long

    With Sheet4
        RowNo = .Cells(.Rows.Count, 4).End(xlUp).Row + 1

        .Cells(RowNo, 2) = Sheet1.Cells(14, 2)
        .Cells(RowNo, 3) = Sheet1.Cells(14, 3)
        .Cells(RowNo, 4) = Sheet1.Cells(14, 4)
        .Cells(RowNo, 5) = Sheet1.Cells(15, 2)
        .Cells(RowNo, 6) = Sheet1.Cells(15, 3)
        .Cells(RowNo, 7) = Sheet1.Cells(15, 4)
        .Cells(RowNo, 8) = Sheet1.Cells(16, 2)
        .Cells(RowNo, 9) = Sheet1.Cells(16, 3)
        .Cells(RowNo, 10) = Sheet1.Cells(16, 4)
        .Cells(RowNo, 11) = Sheet1.Cells(17, 2)

        If RowNo > 1440 Then
             .Rows("2:2").Delete Shift:=xlUp
        End If

        Application.OnTime Now + TimeValue("00:01:00"), "CopyValues"
    End With
End Sub

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim z As Long
    z = 1440

    Sheet4.Range("A2:K" & z).Delete Shift:=xlUp
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Or Target.Column > 11 Then Exit Sub
    With Application
        .EnableEvents = False
        Me.Cells(Target.Row, 1) = Now
        .EnableEvents = True
    End With
End Sub

Upvotes: 1

CLR
CLR

Reputation: 12279

So, as @Xabier mentions, you're not fully qualifying your ranges. You can also create a loop to copy your data as there is a pattern there.

In CopyValues(), replace

RowNo = Sheets(4).Cells(Rows.Count, 4).End(xlUp).Row + 1

Sheets(4).Cells(RowNo, 2) = Sheets(1).Cells(14, 2)
Sheets(4).Cells(RowNo, 3) = Sheets(1).Cells(14, 3)
Sheets(4).Cells(RowNo, 4) = Sheets(1).Cells(14, 4)
Sheets(4).Cells(RowNo, 5) = Sheets(1).Cells(15, 2)
Sheets(4).Cells(RowNo, 6) = Sheets(1).Cells(15, 3)
Sheets(4).Cells(RowNo, 7) = Sheets(1).Cells(15, 4)
Sheets(4).Cells(RowNo, 8) = Sheets(1).Cells(16, 2)
Sheets(4).Cells(RowNo, 9) = Sheets(1).Cells(16, 3)
Sheets(4).Cells(RowNo, 10) = Sheets(1).Cells(16, 4)
Sheets(4).Cells(RowNo, 11) = Sheets(1).Cells(17, 2)
If RowNo > 1440 Then
    Rows("2:2").Delete Shift:=xlUp
End If

with the following:

With ThisWorkbook
    RowNo = .Sheets(4).Cells(Rows.Count, 4).End(xlUp).Row + 1
    For t = 2 To 11
        .Sheets(4).Cells(RowNo, t) = .Sheets(1).Cells(((t - 2) \ 3) + 14, 2 + ((t - 2) Mod 3))
    Next
    If RowNo > 1440 Then .Rows("2:2").Delete Shift:=xlUp
End With

in the BeforeClose() replace:

Worksheets("Sheet4").Range("A2:K" & z).Delete Shift:=xlUp

with

ThisWorkBook.Worksheets("Sheet4").Range("A2:K" & z).Delete Shift:=xlUp

Lastly, for Worksheet_Change, as it's in a Sheet, you just need to change

Cells(Target.Row, 1) = Now

to:

Me.Cells(Target.Row, 1) = Now

Upvotes: 1

Related Questions