title: Run-time error '9' if I open another workbook
body: <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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 &gt; 1440 Then
       Rows("2:2").Delete Shift:=xlUp
  End If
  
  Application.OnTime Now + TimeValue("00:01:00"), "CopyValues"
  
  End Sub 
  </code></pre>
  
  <p>I am also running this on the workbook:</p>
  
  <pre><code>Option Explicit
  
  Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Dim z As Integer
      z = 1440
  
      Worksheets("Sheet4").Range("A2:K" &amp; z).Delete Shift:=xlUp
  End Sub
  </code></pre>
  
  <p>And this on 'Sheet4':</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column = 1 Or Target.Column &gt; 11 Then Exit Sub
      Application.EnableEvents = False
      Cells(Target.Row, 1) = Now
      Application.EnableEvents = True
  End Sub
  </code></pre>