I can't analize entire application.
Try what I wrote and tell if it works or not.
Put breakpoint into line which couses error and try to execute next line
step by step in immediate windows (Ctrl + G)

Check if workbook is realy open, check what is the real name of sheet
which you try to open, etc..







Anthony wrote:
This is the actual code;

Sub Button2_Click()
'
' Button2_Click Macro
' Macro recorded 21/05/2006 by Anthony & Ehly
'

'
Dim OpenFilename As Variant
Dim a, b, c, d, e As Integer
Dim fname, officename As String
Dim nocount As Integer

On Error GoTo error

Application.EnableEvents = False
Application.ScreenUpdating = False

OpenFilename = Application.GetOpenFilename("Excel Files (*.xls),*.xls",
, "Open tracker files", , True)

If IsArray(OpenFilename) Then

For a = LBound(OpenFilename) To UBound(OpenFilename)

Workbooks.Open (OpenFilename(a))

For b = 1 To Len(OpenFilename(a))

If (Mid$(OpenFilename(a), b, 1) = "\") Then

c = b

End If

Next b

fname = Right$(OpenFilename(a), Len(OpenFilename(a)) - c)
fname = Left$(fname, Len(fname) - 4)


Application.ThisWorkbook.Sheets("sheet1").Visible = True

e = Application.ThisWorkbook.Sheets("sheet1").Cells(1, 1).Value + 2

If (Workbooks(fname).Sheets("sheet1").Cells(1, 2).Value = 492507)
Then

Workbooks(fname).Sheets("sheet1").Visible = True
d = Workbooks(fname).Sheets("sheet1").Cells(1, 1).Value
officename = Workbooks(fname).Sheets("sheet1").Cells(1, 3).Value

If (d 0) Then
Workbooks(fname).Sheets("sheet1").Select
Range(Cells(2, 1), Cells(d + 1, 5)).Select
Selection.Copy
Application.ThisWorkbook.Activate
Sheets("sheet1").Select
Cells(Sheets("sheet1").Cells(1, 1).Value + 2, 1).Select
ActiveSheet.Paste

'copy front page
Workbooks(fname).Activate
Sheets("absence").Select
Range("A2:B" & (d + 1)).Select

Selection.Copy
Application.ThisWorkbook.Activate
Sheets("absence").Select
Range("B" & e & ":C" & (e + d - 1)).Select
Selection.PasteSpecial Paste:=xlPasteValues

Workbooks(fname).Activate
Sheets("absence").Select
Range("D2:D" & (d + 1)).Select

Selection.Copy
Application.ThisWorkbook.Activate
Sheets("absence").Select
Range("E" & e & ":E" & (e + d - 1)).Select
Selection.PasteSpecial Paste:=xlPasteValues

Workbooks(fname).Activate
Sheets("absence").Select
Range("F2:T" & (d + 1)).Select

Selection.Copy
Application.ThisWorkbook.Activate
Sheets("absence").Select
Range("G" & e & ":U" & (e + d - 1)).Select

Selection.PasteSpecial Paste:=xlPasteValues

For c = e To (e + (d - 1))

Range("A" & c).Value = officename

Next

End If

End If

error: If (Not IsEmpty(fname)) Then
Workbooks(fname).Close savechanges:=False
End If

Application.ThisWorkbook.Sheets("sheet1").Visible = False


Next

End If

Application.EnableEvents = True
Application.ScreenUpdating = True
Sheet1.Worksheet_Calculate

End Sub

is the calling the worksheet a regonal problem like you stated, as this does
work on 99.9% of computers I have run it on.


"witek" wrote in message
...

Anthony wrote:

How do I get around this?



check codename of this worksheet. It is (Name) property in properties (in
VBE) . Probably it is Sheet1 or something around that and write.


Workbooks(fname).Sheet1.select









"witek" wrote in message
...


Anthony wrote:


Hi,

The problem I am having is only occurring on 2 pc's. I have run the
code on numerous other machines with no issues, but the annoying thing
is I need it to run it on one of the pc's that is having the subscript
out of range error.

I have attached the two spreadsheets. The master file is meant to open
up the office file and load in any information. The code is written so
the user can select multiple files and it will work its way through
them.

The code is not very fancy but it was something I was putting together
in a hurry and I was going to go back through the code and clean it up
etc.

What I don't understand is on the machines it failed on, the code in
module 2 (Button2_click) fails during the function; e.g (sample of what
I have descriped)


fname="Tracker file"
Workbooks.open(fname) 'ok
Workbooks(fname).sheets("sheet1").select 'fails - subscript out of
range

Any help would be gratefully received.

I apologise for having to attach files.

Anthony

p.s password is dragon1



different regional setting and "sheet1" is not "sheet1" ?