subscript out of range error

[shailee]

hello!
i have been trying to copy formatting from worksheet A in one workbook to worksheet B in a different workbook using a macro. Following is the script:

Sub oldnewdif()
'to find the difference between two sheets

Range("A1:AH500").Formula = _
"=IF(ISBLANK([WB1.xls]Sources!RC),"""",IF(ISTEXT([WB1.xls]Sources!RC),T([WB1.xls]Sources!RC),[WB1.xls]Sources!RC-[WB2.xls]Sources!RC))"
Windows("WB1.xls.xls").Activate
Cells.Select
Range("A1:AH500").Activate
Selection.Copy
Windows("New Microsoft Excel Worksheet.xls").Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows("WB1.xls.xls").Activate
Windows("New Microsoft Excel Worksheet.xls").Activate

End Sub


On running it there is always an error that says "subscript out of range". Could someone please tell me how to resolve this?

Thanks
Shailee

 

[namliam]

Might be to obvious, but is this right??
WB1.xls.xls

 

[shailee]

Yes..actually the file name is WB1.xls and the second xls is its extension.

 

[namliam]

the only thing I can think of without knowing what line its failing on.... is you shouldnt need the extension in any of the filenames... so...
Windows("WB1.xls.xls").Activate
should be: Windows("WB1.xls").Activate

Windows("New Microsoft Excel Worksheet.xls").Activate
Should be Windows("New Microsoft Excel Worksheet").Activate

 

[shailee]

tried..but ain't working..

 

[namliam]

what line is your error occuring?

 

[shailee]

the line that says Windows("WB1.xls.xls").Activate

 

[namliam]

Which probably means either/or
1) the file is not called that way
2) the file is not currently open

 

[shailee]

so what do you mean by "the file isnt called that way?"i mean how can i correct this?

 

[Brianwarnock]

WB1.xls is a strange file name, surely it is WB1 with xls as its extension.

Brian

 

[boblarson]

I think you'll find that it will work okay if you change the file name to remove the last .xls part so that it is only WB1.xls

VBA in any of the Office products is a bit touchy on having double extensions. It doesn't like it.

 

[shailee]

hi boblarson!
i tried doing what you suggested ...still not working unfortunately..

 

[wiklendt]

have you tried using .Open instead of .Activate?