Run-time error 9 in VBA script

Question

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.

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.

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!

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

Error 9 comes in this line Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text

    Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text
    Pit = Mid(Name, 4, 2)
    RL = Mid(Name, 7, 4)
    Pattern = Right(Name, 4)
    Pts = "" & Pit & "_" & RL & "_" & Pattern & "_pts.csv"


'Adding new workbook with a proper name

Set NewBook = Workbooks.Add
    With NewBook
    .SaveAs Filename:="" & Pts & ""

    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" & Rows) = "" & Pit & ""
    Range("B2:B" & Rows) = "" & RL & ""
    Range("C2:C" & Rows) = "" & Pattern & ""

'Copying data for easting, northing, rl and point number from original spreadsheet to the new one

    Workbooks(oBook).Sheets(oSheet).Activate
    Range("C2:C" & Rows).Select
    Selection.Copy

    NewSheet.Activate
    Range("D2").PasteSpecial Paste:=xlPasteValues

    Workbooks(oBook).Sheets(oSheet).Activate
    Range("E2:E" & Rows).Select
    Selection.Copy

    NewSheet.Activate
    Range("H2").PasteSpecial Paste:=xlPasteValues

    Workbooks(oBook).Sheets(oSheet).Activate
    Range("G2:I" & Rows).Select
    Selection.Copy

    NewSheet.Activate
    Range("E2").PasteSpecial Paste:=xlPasteValues

    Workbooks(NewBook).Sheets(NewSheet).Save

End With

End Sub

UPDATE

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:

Name = ActiveSheet.Range("A2").Text

No I don't get Error 9 but I'm getting Error 13: Type mismatch if the following line:

Set NewSheet = Workbooks(NewBook).Sheets("Sheet1")

Any clues on what is wrong here? Thanks again!

Answer 1

Thanks for the help! Everything has solved itself when I used different approach in referring to workbooks. It looks strange but it seems to work fine:

Set wsNewSheet = Workbooks.Open("C:\Minestar_exports\" & Pts & "")
Set wsO_Sheet = Workbooks.Open("" & OldBookName & "")

I figured that I didn't have to refer to a specific worksheet if I'm only interested in the default one and it works great now!

Answer 2

In response to the comment that you put under David's answer...

This:

DataBook = ThisWorkbook.Name
DataSheet = ActiveSheet.Name

is potentially dangerous when combined with this:

Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text

Reason? Think about it for a sec. It should work fine IF the workbook that the code is embedded in is ALSO the current workbook. However if any OTHER workbook is active at the time that the code is executed then the variable DataSheet will pick up the name of a sheet in THAT workbook, not one in the "ThisWorkbook" object. Consequently you could be asking VBA to find a sheet name that certainly does exist in the ACTIVE workbook at the time, but may not exist in "ThisWorkbook"; that is, the workbook that the code is running from.

That will in turn give you a subscript out of range error or, to put it another way, "you're looking for a sheet in that does not exist in this workbook". (And again I emphasise, "ThisWorkbook" is the book that the code runs from, NOT (necessarily) the active workbook at the time.)

Either change the DataBook reference to the Active workbook, or activate ThisWorkbook (depending on what your intention is) to make sure that the sheet is in the same workbook that you're referring to.

Answer 3

NewBook is alreayd a workbook object so the correct way to do this assignment is like Set NewSheet = NewBook.Sheets("Sheet1")

The construct Workbooks(_something_) takes a string argument, as the name of a workbook. What you're passing instead of a string is a workbook object, which will raise an error since it is not the expected data type.

For grins, you could do:

... = Workbooks(NewBook.Name).Sheets("Sheet1")

But that is obviously redundant, and kind of defeats the purpose of using object variables in your code.

NOTE Name is also a semi-reserved word, since it's a type of object in Excel. While this may not raise any errors for you, it might be confusing, and personally I try to avoid using variable names that are identical to, or very similar to objects.