You might be trying to make something harder than it need be cuz this will
work
sub gothere()
x = [roster!e7]
Application.Goto Sheets(x).Range("b5")
end sub

but this works too
Sub addNewList()
Dim mySheetName As String
'Dim mySheet As Worksheet
Dim myRange As Range
Dim numOfWeeks As Integer
Dim numOfShifts As Integer
Dim baseCell As Range
Dim cellFormula As String
Dim initials As String

'Initialize variables
mySheetName = Worksheets("ROSTER").Range("E7").Value
'Set mySheet = Worksheets(mySheetName)
numOfWeeks = Worksheets("Roster").Range("E10").Value
numOfShifts = numOfWeeks * 17

'Get to our starting point
Sheets(mySheetName).Select
Set myRange = Range("B5")
myRange.Select
End Sub

--
Don Guillett
SalesAid Software

"jason" wrote in message
...
Hi, I'm trying to use VB to alter one worksheet based on a
roster of names in another worksheet (in the same
workbook). I'm using Excel 2000 version 9.0.2720 (Windows
2000) and Visual Basic 6.0.8435.

Each time the code gets to a place (any place) where it is
using a range, the code stops executing and VB reports the
following: "Visual Basic 400". Nothing else! Just -
"400."

I suspect that, since I'm new to VB, I'm not setting
things up correctly. Here is a typical function (see ***
for the line where things are breaking down):

Private Sub addNewList(ByVal numEmpl As Integer)

Dim mySheetName As String
Dim mySheet As Worksheet
Dim myRange As Range

Dim numOfWeeks As Integer
Dim numOfShifts As Integer
Dim baseCell As Range
Dim cellFormula As String
Dim initials As String

'Initialize variables
mySheetName = Worksheets("ROSTER").Range("E7").Value
Set mySheet = Worksheets(mySheetName)
numOfWeeks = Worksheets("Roster").Range("E10").Value
numOfShifts = numOfWeeks * 17

'Get to our starting point
mySheet.Select
Set myRange = Range("B5")
*** myRange.Select

If anyone can help, I would greatly appreciate it.