If you saved the file as a .xlsx extension, why are you trying to reference
it with a .xlxm extension? You need to understand what these extensions
mean. They define the type of file. xlsx is for data only, no macro. xlsm
is for files with macros. VBA sees these extensions as different files even
if the rest of the name is the same. So if you have the file saved as xlsx
and try to reference it as xlsm, you will get a message "Subscript out of
range."

"Elizabeth" wrote:

I saved my original file to .xlsx, that fixed my run time '53' error. I now
getting a Runtime error '9' subscript out of range in this part of the code:

Sheets("DECG").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
'ActiveWindow.LargeScroll ToRight:=-3
Range("J2").Select
Range("A1:BG10000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _

Workbooks("FormatReport2.xlsm").Sheets("Selections ").Range("A11:A13"),
Unique:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Windows("c").Activate (runtime error occurs here)

"JLGWhiz" wrote:

When you work with two different versions of Excel that have different file
extensions, it means that you have to use the right extension in the right
place. Running the Delete command before the SaveAs command can be
problematic in that the file you are trying to delete is xl2003 with the .xls
extension and you want to save it as xl2007 with a .xlsm extension. If you
have not yet executed the SaveAs, the .xlsm does not yet exist, so you will
get a "File Not Fou7nd" message.
So the delete extension should be .xls and the SaveAs extension should be
.xlsm/

"Elizabeth" wrote:

I changed the file format to xlExcel8 and that fixed the runtime error '1004'
but now I getting a Runtime error '53' on this part of the code:

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xlsm"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xlsm", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xlsm"


"JLGWhiz" wrote:

FOLDER & "OrderStatusTemp.xls", FileFormat:= _

This is one of the lines I am referring to. There is one more that needs
changing.

FOLDER & "OrderStatusTemp.xlsm", FileFormat:= _


"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.