Help : Run-Time Error '9' Subscript out of range

[Lorezt]

Hey,

I need help. I am very new at this and im not that literate with codings.

I was given an excel file to generate random data for my assignment to be keyed into Microsoft Access.

So I had to key in my student ID and set the location file (where to save the random new data that is going to be created) and as I click the generate the data was created in a new sheet but an error box popped out saying "Run-Time Error '9' Subscript out of range".

So then I click debug and the coding came out:

Sub AddNew()
Dim StudID As String
Dim WBFilename As String
Dim ThisFile As String
Dim WBlocation As String




'identify this main file
ThisFile = ActiveWorkbook.Name

'get Student ID, create filename for new workbook
StudID = Range("StudentID").Text
If Left(StudID, 1) = "s" Or Left(StudID, 1) = "S" Then
MsgBox "Do not put an 's' in front of your student number", vbExclamation, "Remove the s in your Student Number"
Else

If Len(StudID) > 0 Then


WBlocation = Range("NewFileLocation").Text
WBFilename = StudID & "DBFile.xls"


'create new book
Set newBook = Workbooks.Add
    With newBook
        .Title = WBFilename
        .Subject = "BCO1102"
        .SaveAs Filename:=WBlocation & WBFilename
    End With


'Back to main book
Workbooks(ThisFile).Activate

'select sales data and copy
Worksheets("Sales").Activate
    Range("DataStart").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
'go to new book and paste sales data
Workbooks(WBFilename).Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    ' set format of date data
       Range("B2").Select
       Range(Selection, Selection.End(xlDown)).Select
       Selection.NumberFormat = "d-mmm-yy"
       Range("A1").Select
  
  'Rename sheets, delete sheet 3
    Sheets("Sheet1").Select
       Sheets("Sheet1").Name = "Sales"
    Sheets("Sheet2").Select
       Sheets("Sheet2").Name = "Customers"
    Sheets("Sheet3").Select
       Sheets("Sheet3").Name = "Stores"
    
   'turn off alert message, delete sheet3, turn on alert
    'Application.DisplayAlerts = False
        'Worksheets("Sheet3").Delete
    'Application.DisplayAlerts = True
    
 'Back to main file
 Workbooks(ThisFile).Activate

'select customer data and copy
Worksheets("Customers").Activate
Range("CustomerTable").Select
Selection.Copy

'go to new workbook paste into customers sheet
Workbooks(WBFilename).Activate
    Sheets("Customers").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    
'Back to main file
 Workbooks(ThisFile).Activate

'select Stores data and copy
Worksheets("Stores").Activate
Range("OutletTable").Select
Selection.Copy

'go to new workbook paste into Stores sheet
Workbooks(WBFilename).Activate
    Sheets("Stores").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1").Select

'put focus on first sheet, A1
    Sheets("Sales").Select
    Range("A1").Select
    
    'select Instuctions worksheet, copy paste
   Workbooks(ThisFile).Activate
   Worksheets("Instructions").Activate
    Range("A1:C18").Select
    Selection.Copy
    Workbooks(WBFilename).Activate
    Sheets.Add
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "Instructions"
    Range("A1").Select
    ActiveSheet.Paste
    Columns("C:C").ColumnWidth = 76.14
    Columns("C:C").ColumnWidth = 9.43
    Columns("B:B").ColumnWidth = 100.57
    Rows("4:4").RowHeight = 37.5
    Rows("8:8").RowHeight = 40.5
    Rows("8:8").RowHeight = 27.75
    ActiveWindow.SmallScroll Down:=6
    Rows("12:12").RowHeight = 33
    ActiveWindow.SmallScroll Down:=-15
    Rows("15:15").RowHeight = 8.25
    Columns("C:C").ColumnWidth = 5.43
    ActiveWindow.SmallScroll Down:=15
    Rows("17:18").RowHeight = 0
    Rows("16:16").RowHeight = 96.75
    ActiveWindow.SmallScroll Down:=-33
    Range("A1").Select

'save new workbook
    ActiveWorkbook.Save
    
'Go back to main workbook and close it.
    Workbooks(WBFilename).Activate
    Workbooks(ThisFile).Activate
    ActiveWorkbook.Close (False)

Else
Beep
Beep
Beep

Range("StudentID").Select

End If
End If


End Sub 

The debug highlighted "Workbooks(WBFilename).Activate". Im guessing there's a problem with this code?

Please help. I need the data extracted from this file to complete my assignment.

Thanks.

[gep13]

Moved to the Office Development Forum.

Gary

[jmsrickland]

I'm just taking a guess here but you have WBFilename defined as a String and you load it with a string WBFilename = StudID & "DBFile.xls" and then you use it as an Index value in Workbooks(WBFilename).Activate.

[westconn1]

I'm just taking a guess here but you have WBFilename defined as a String and you load it with a string WBFilename = StudID & "DBFile.xls" and then you use it as an Index value in Workbooks(WBFilename).Activate.

that appears to be correct and should work, though you need to be sure that wblocation has a trailing \
but as you have that workbook set to a workbook object, you should be working with the newbook object you have

also you should not select or activate any workbook or sheet, unless it is to present it to the user when the code finishes

instead of any code like

sheets("mysheet").activate
range("a1").select
selection.value = 99
'or
activecell.value = 99

newbook.sheets("mysheet").range("a1").value = 99

if you are doing a lot of work in specific sheets, set them to objects, then you no longer have to even use the workbook object

while recorded macros are fantastic to get code of doing stuff, they are really only sample code and need to be modified, using the selection object or activate, causes problems, that may not show for some time, also make the code run considerably slower, the scrolldowns are also not required, they are just part of a recorded macro, so you can see the part you want to work with, but can be removed