I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


"Free Agent99" wrote:

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

"Free Agent99" wrote:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

Miguel,

I have had someone helping me test this here. It seems that what is
happening is that the command Windows("broadspire.xls").Activate is not
working. So that when the macro does the Sheets command, I get the subscript
out of range error.

But it only does this on certain users.?????


Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


"Free Agent99" wrote:

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

"Free Agent99" wrote:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

If that is the error, you may try to substitute the line with:
Application.Workbooks("broadspire.xls").Activate

Miguel.

"Free Agent99" wrote:

Miguel,

I have had someone helping me test this here. It seems that what is
happening is that the command Windows("broadspire.xls").Activate is not
working. So that when the macro does the Sheets command, I get the subscript
out of range error.

But it only does this on certain users.?????


Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


"Free Agent99" wrote:

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

"Free Agent99" wrote:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

I already tried that. But with some more testing, it looks like the error is
caused by having the .xls file and .csv file named the same. Seems to confuse
Excel 2003. We changed the name of the CSV file and now it seems to work.
Just a wild guess and we changed the Broadspire.Csv to test.csv and did not
get the error.

Thanks,

Tod Brannen

"Miguel Zapico" wrote:

If that is the error, you may try to substitute the line with:
Application.Workbooks("broadspire.xls").Activate

Miguel.

"Free Agent99" wrote:

Miguel,

I have had someone helping me test this here. It seems that what is
happening is that the command Windows("broadspire.xls").Activate is not
working. So that when the macro does the Sheets command, I get the subscript
out of range error.

But it only does this on certain users.?????


Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


"Free Agent99" wrote:

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

"Free Agent99" wrote:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

Good that you finally found the reason! I have to admit that I haven't
tought of that.

Best,
Miguel.

"Free Agent99" wrote:

I already tried that. But with some more testing, it looks like the error is
caused by having the .xls file and .csv file named the same. Seems to confuse
Excel 2003. We changed the name of the CSV file and now it seems to work.
Just a wild guess and we changed the Broadspire.Csv to test.csv and did not
get the error.

Thanks,

Tod Brannen

"Miguel Zapico" wrote:

If that is the error, you may try to substitute the line with:
Application.Workbooks("broadspire.xls").Activate

Miguel.

"Free Agent99" wrote:

Miguel,

I have had someone helping me test this here. It seems that what is
happening is that the command Windows("broadspire.xls").Activate is not
working. So that when the macro does the Sheets command, I get the subscript
out of range error.

But it only does this on certain users.?????


Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


"Free Agent99" wrote:

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

"Miguel Zapico" wrote:

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

"Free Agent99" wrote:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen