VB Code generates error on another machine?

[Guest]

Hi,

I am currently using Excel 2003 here and have completed a pretty basic VB
code that gets data from one file, sorts, cuts & pastes to a master workbook
and then creates a new workbook from that data based on user input.

The problem I am having is that this little program works perfectly fine on
my Work PC and my PC at home but when someone else at work tries to run the
code it keeps crashing with a Subscript out of range error?

Why does this work on my PCs and not on my workmates who is going to use
this program???? Really frustrating! PLease can anyone help?

 

[Dave Peterson]

Include the extension in the filename:

workbooks("book1.xls").activate
not just
workbooks("book1").activate

(or windows(...)...)

 

[Guest]

Thanks for your quick reply, I have checked your advice with my code but the
thing still doesn't work. COuld you please look at this and let me know what
might be the prob!

Sub COPYRAWDATA()

PROJYEAR = Worksheets("WORKING").Cells(9, 8)
PROJMONTH = Worksheets("WORKING").Cells(99, 1)
RAWFILENAME = "RAW DATA.XLS"
CHECKRAWFILE = Dir("G:\Admin\Timepro\Report AutoMATE\" & RAWFILENAME)

If CHECKRAWFILE = "" Then
MsgBox "RAW DATA FILE NOT FOUND"
Worksheets("WORKING").Cells(105, 6) = "NO"
Else
Worksheets("WORKING").Cells(105, 6) = "YES"

Workbooks.Open ("G:\Admin\Timepro\Report AutoMATE\" & RAWFILENAME)
MsgBox "CODE HAS REACHED HERE WITH NO ERRORS!"

'*************it gets here and error 9 *******************

Workbooks(RAWFILENAME).Sheets("Sheet1").Range("A1:J20000").Copy _
Destination:=Workbooks("Timepro Report AutoMATE").Sheets("RAW
DATA").Range("A1:J20000")

Workbooks("Timepro Report AutoMATE").Worksheets("WORKING").Activate
Workbooks(RAWFILENAME).Close

End If
End Sub

Thanks for your help.

 

[Dave Peterson]

The first thing I'd try is to include the .xls with both the "timepro report
automate.xls" references:

Workbooks(RAWFILENAME).Sheets("Sheet1").Range("A1:J20000").Copy _
Destination:=Workbooks("Timepro Report AutoMATE") _
.Sheets("RAW DATA").Range("A1:J20000")

And this one, too:
Workbooks("Timepro Report AutoMATE").Worksheets("WORKING").Activate
Workbooks(RAWFILENAME).Close

 

[Guest]

Thanks so very very much, I thought it was going to be something simple. You
are a life saver.

I guess I owe ya a beer or two.