title: Run-time error 9 in VBA script
body: <p>So basically I probably have a lame question. I've used VBA for about two hours now and I'm just trying to make a macro that would would create a new speadsheet and copy data from existing one.</p>
  
  <p>I've dealt with compilation errors there were, but now when I try to compile the project I get a "Run-time Error 9: Subscript out of range". In the code below the error comes up in the line where Name variable gets assigned its value. </p>
  
  <p>I've had a look at other threads with the same issue but with my limited knowledge of VBA I couldn't figure out what is wrong or whether this code would work at all. Any help will be greatly appreciated!
  Thanks everyone!</p>
  
  <pre><code>Option Explicit
  
  Sub PointsCopy()
  
  
  'Declaring variables for correct naming
  
      Dim Pit As String
      Dim RL As Integer
      Dim Pattern As Integer
      Dim Name As String
      Dim DataBook As String
      Dim DataSheet As String
      Dim oBook As Workbook
      Dim oSheet As Worksheet
      Dim NewBook As Workbook
      Dim NewSheet As Worksheet
      Dim Rows As Integer
      Dim Pts As String
  
  
  
  'Figuring out active workbook and worksheet
  
      Set oBook = ActiveWorkbook
      Set oSheet = ActiveSheet
  
      DataBook = ThisWorkbook.Name
      DataSheet = ActiveSheet.Name
  
  
  'Getting pit, RL and pattern name from cell A2 and assigning to variable
  </code></pre>
  
  <p>Error 9 comes in this line
          <strong>Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text</strong></p>
  
  <pre><code>    Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text
      Pit = Mid(Name, 4, 2)
      RL = Mid(Name, 7, 4)
      Pattern = Right(Name, 4)
      Pts = "" &amp; Pit &amp; "_" &amp; RL &amp; "_" &amp; Pattern &amp; "_pts.csv"
  
  
  'Adding new workbook with a proper name
  
  Set NewBook = Workbooks.Add
      With NewBook
      .SaveAs Filename:="" &amp; Pts &amp; ""
  
      Set NewSheet = Workbooks(NewBook).Sheets("Sheet1")
  
  'Activating new worksheet
  NewSheet.Activate
  
  'Adding column names to the new workbook
  
      Range("A1").Value = "MQ2_PIT_CODE"
      Range("B1").Value = "BLOCK_TOE"
      Range("C1").Value = "PATTERN_NUMBER"
      Range("D1").Value = "BLOCK_NAME"
      Range("E1").Value = "EASTING"
      Range("F1").Value = "NORTHING"
      Range("G1").Value = "RL"
      Range("H1").Value = "POINT_NO"
  
  'Activate original data sheet
  
      Workbooks(oBook).Sheets(oSheet).Activate
  
  'Count number of data rows in the original spreadsheet
  
      Rows = Application.Count(Range("A2:A"))
  
  'Activate the new spreadsheet and enter pit code, block toe and pattern number
  
      NewSheet.Activate
      Range("A2:A" &amp; Rows) = "" &amp; Pit &amp; ""
      Range("B2:B" &amp; Rows) = "" &amp; RL &amp; ""
      Range("C2:C" &amp; Rows) = "" &amp; Pattern &amp; ""
  
  'Copying data for easting, northing, rl and point number from original spreadsheet to the new one
  
      Workbooks(oBook).Sheets(oSheet).Activate
      Range("C2:C" &amp; Rows).Select
      Selection.Copy
  
      NewSheet.Activate
      Range("D2").PasteSpecial Paste:=xlPasteValues
  
      Workbooks(oBook).Sheets(oSheet).Activate
      Range("E2:E" &amp; Rows).Select
      Selection.Copy
  
      NewSheet.Activate
      Range("H2").PasteSpecial Paste:=xlPasteValues
  
      Workbooks(oBook).Sheets(oSheet).Activate
      Range("G2:I" &amp; Rows).Select
      Selection.Copy
  
      NewSheet.Activate
      Range("E2").PasteSpecial Paste:=xlPasteValues
  
      Workbooks(NewBook).Sheets(NewSheet).Save
  
  End With
  
  End Sub
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I've figured out why I was having this error - I was referring to a workbook and worksheet with String type of variable, so I've changed the erroring line the following way:</p>
  
  <pre><code>Name = ActiveSheet.Range("A2").Text
  </code></pre>
  
  <p>No I don't get <strong>Error 9</strong> but I'm getting <strong>Error 13: Type mismatch</strong> if the following line:</p>
  
  <pre><code>Set NewSheet = Workbooks(NewBook).Sheets("Sheet1")
  </code></pre>
  
  <p>Any clues on what is wrong here? Thanks again!</p>