|
error message
I have a macro in an Access database that opens a new Excel workbook and
writes data to a sheet in the workbook. The macro then does "stuff" to the data (sort, total, etc.) I'm receiving an error message when the macro sorts. The message I get is: "Method 'Range' of object_Global' Failed". Following is the code used to sort the data: ..Range("A1").Select ..Range(.ActiveCell, .ActiveCell.Offset(0, 2)).Select ..Range(.Selection, .Selection.End(xlDown)).Select ..Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _:=xlSortNormal ..Range("A1").Select I don't get this error all of the time. Is there anything I can change or add to the code so I don't get this error. Thanks for the help and suggestions.......... -- JT |
error message
When do you get the error? What is the one thing that the sheet has in common
each time the error is generate? Is there no data on the sheet, or ... The only suspisciosu thing I see in the code is that you have set Header:=xlGuess which seems odd since I would think you would know ahead of time if the data ws going to have a header or not... -- HTH... Jim Thomlinson "JT" wrote: I have a macro in an Access database that opens a new Excel workbook and writes data to a sheet in the workbook. The macro then does "stuff" to the data (sort, total, etc.) I'm receiving an error message when the macro sorts. The message I get is: "Method 'Range' of object_Global' Failed". Following is the code used to sort the data: .Range("A1").Select .Range(.ActiveCell, .ActiveCell.Offset(0, 2)).Select .Range(.Selection, .Selection.End(xlDown)).Select .Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _:=xlSortNormal .Range("A1").Select I don't get this error all of the time. Is there anything I can change or add to the code so I don't get this error. Thanks for the help and suggestions.......... -- JT |
error message
I have changed the header to "Yes". The data is written to the workbook as a
result of a transfer spreadsheet command. The only thing that have seen is that the there is a range name as a result of the transfer and I now remove that each time. The only consistent thing is that the code runs without any problems. I then close Excel and rerun the macro and I receive the error. If I click "End" and re-run the macro, it works. But if I try to run the macro again, I get the error. It almost appears to me that something hasn't been reset and that is causing the error. Any other suggestions would be appreciated. Thanks again for all of your help.. -- JT "Jim Thomlinson" wrote: When do you get the error? What is the one thing that the sheet has in common each time the error is generate? Is there no data on the sheet, or ... The only suspisciosu thing I see in the code is that you have set Header:=xlGuess which seems odd since I would think you would know ahead of time if the data ws going to have a header or not... -- HTH... Jim Thomlinson "JT" wrote: I have a macro in an Access database that opens a new Excel workbook and writes data to a sheet in the workbook. The macro then does "stuff" to the data (sort, total, etc.) I'm receiving an error message when the macro sorts. The message I get is: "Method 'Range' of object_Global' Failed". Following is the code used to sort the data: .Range("A1").Select .Range(.ActiveCell, .ActiveCell.Offset(0, 2)).Select .Range(.Selection, .Selection.End(xlDown)).Select .Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _:=xlSortNormal .Range("A1").Select I don't get this error all of the time. Is there anything I can change or add to the code so I don't get this error. Thanks for the help and suggestions.......... -- JT |
error message
Which line is it stopping at...
-- HTH... Jim Thomlinson "JT" wrote: I have changed the header to "Yes". The data is written to the workbook as a result of a transfer spreadsheet command. The only thing that have seen is that the there is a range name as a result of the transfer and I now remove that each time. The only consistent thing is that the code runs without any problems. I then close Excel and rerun the macro and I receive the error. If I click "End" and re-run the macro, it works. But if I try to run the macro again, I get the error. It almost appears to me that something hasn't been reset and that is causing the error. Any other suggestions would be appreciated. Thanks again for all of your help.. -- JT "Jim Thomlinson" wrote: When do you get the error? What is the one thing that the sheet has in common each time the error is generate? Is there no data on the sheet, or ... The only suspisciosu thing I see in the code is that you have set Header:=xlGuess which seems odd since I would think you would know ahead of time if the data ws going to have a header or not... -- HTH... Jim Thomlinson "JT" wrote: I have a macro in an Access database that opens a new Excel workbook and writes data to a sheet in the workbook. The macro then does "stuff" to the data (sort, total, etc.) I'm receiving an error message when the macro sorts. The message I get is: "Method 'Range' of object_Global' Failed". Following is the code used to sort the data: .Range("A1").Select .Range(.ActiveCell, .ActiveCell.Offset(0, 2)).Select .Range(.Selection, .Selection.End(xlDown)).Select .Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _:=xlSortNormal .Range("A1").Select I don't get this error all of the time. Is there anything I can change or add to the code so I don't get this error. Thanks for the help and suggestions.......... -- JT |
error message
this is the line it is stopping on:
..Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _:=xlSortNormal Thanks again.......... -- JT "Jim Thomlinson" wrote: Which line is it stopping at... -- HTH... Jim Thomlinson "JT" wrote: I have changed the header to "Yes". The data is written to the workbook as a result of a transfer spreadsheet command. The only thing that have seen is that the there is a range name as a result of the transfer and I now remove that each time. The only consistent thing is that the code runs without any problems. I then close Excel and rerun the macro and I receive the error. If I click "End" and re-run the macro, it works. But if I try to run the macro again, I get the error. It almost appears to me that something hasn't been reset and that is causing the error. Any other suggestions would be appreciated. Thanks again for all of your help.. -- JT "Jim Thomlinson" wrote: When do you get the error? What is the one thing that the sheet has in common each time the error is generate? Is there no data on the sheet, or ... The only suspisciosu thing I see in the code is that you have set Header:=xlGuess which seems odd since I would think you would know ahead of time if the data ws going to have a header or not... -- HTH... Jim Thomlinson "JT" wrote: I have a macro in an Access database that opens a new Excel workbook and writes data to a sheet in the workbook. The macro then does "stuff" to the data (sort, total, etc.) I'm receiving an error message when the macro sorts. The message I get is: "Method 'Range' of object_Global' Failed". Following is the code used to sort the data: .Range("A1").Select .Range(.ActiveCell, .ActiveCell.Offset(0, 2)).Select .Range(.Selection, .Selection.End(xlDown)).Select .Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _:=xlSortNormal .Range("A1").Select I don't get this error all of the time. Is there anything I can change or add to the code so I don't get this error. Thanks for the help and suggestions.......... -- JT |
| All times are GMT +1. The time now is 07:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com