Method 'Range' of object '_Global' failed
 

I have a macro which must run when the excel document is opened. The
macro formats the pre-existing excel sheet. In my 'ThisWorkbook' I have
the following code

Private Sub Workbook_Open()
Call Rolling25Months
End Sub

and the Rolling25Months has the following code


Sub Rolling25Months()

Application.ScreenUpdating = False

Dim cTitle

Range("E2:M2").Select

With Selection
..HorizontalAlignment = xlGeneral
..VerticalAlignment = xlBottom
..WrapText = False
..Orientation = 0
..AddIndent = False
..ShrinkToFit = False
..MergeCells = True
End With
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.LargeScroll Down:=1
Range("AA43").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.LargeScroll ToRight:=-1
Range("E2:M2").Select
ActiveSheet.Paste

etc etc............................

Application.ScreenUpdating = True

End Sub


I get the error on this line:

Range("E2:M2").Select

Any ideas on what I am doing wrong will be greatly appreciated!

Thanks in advance.


--
fundoo_pc
------------------------------------------------------------------------
fundoo_pc's Profile: http://www.excelforum.com/member.php...o&userid=30535
View this thread: http://www.excelforum.com/showthread...hreadid=501867

Method 'Range' of object '_Global' failed
 

f,

Qualify the Range with the sheet and don't select the range...

With Worksheets("Preexisting").Range("E2:M2")
.HorizontalAlignment = xlGeneral
' more here - notice the dot prefix
End With

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"fundoo_pc" wrote in message
I have a macro which must run when the excel document is opened. The
macro formats the pre-existing excel sheet. In my 'ThisWorkbook' I have
the following code

Private Sub Workbook_Open()
Call Rolling25Months
End Sub

and the Rolling25Months has the following code
Sub Rolling25Months()
Application.ScreenUpdating = False
Dim cTitle
Range("E2:M2").Select

With Selection
HorizontalAlignment = xlGeneral
VerticalAlignment = xlBottom
WrapText = False
Orientation = 0
AddIndent = False
ShrinkToFit = False
MergeCells = True
End With
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.LargeScroll Down:=1
Range("AA43").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.LargeScroll ToRight:=-1
Range("E2:M2").Select
ActiveSheet.Paste
etc etc............................
Application.ScreenUpdating = True
End Sub


I get the error on this line:
Range("E2:M2").Select
Any ideas on what I am doing wrong will be greatly appreciated!
Thanks in advance.
--
fundoo_pc

Method 'Range' of object '_Global' failed
 

I get the error on this line:

Range("E2:M2").Select

Any ideas on what I am doing wrong will be greatly appreciated!

Easy. It wants to know where Range("E2:M2") is.
What sheet? Even if there is only one sheet in the workbook, you should
specify a sheet. You mean ActiveSheet? Tell it so (and hope that a sheet is
really Active, and that it isn't a Chart...).

"Method...failed", especially with any kind of Range object, usually means
"I need more specific information"

BTW #1: you take a speed/performance hit when you use "Select". Select and
Selection are almost always unneccessary (the macro recorder is incredibly
verbose).
With ActiveSheet.Range("E2:M2")
...
ActiveSheet.Range("AA43").Copy
ActiveSheet.Range("E2:M2").Paste
BTW #2: The "ActiveWindow.LargeScrolls" can probably all be deleted too.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"fundoo_pc" wrote
in message ...

I have a macro which must run when the excel document is opened. The
macro formats the pre-existing excel sheet. In my 'ThisWorkbook' I have
the following code

Private Sub Workbook_Open()
Call Rolling25Months
End Sub

and the Rolling25Months has the following code


Sub Rolling25Months()

Application.ScreenUpdating = False

Dim cTitle

Range("E2:M2").Select

With Selection
HorizontalAlignment = xlGeneral
VerticalAlignment = xlBottom
WrapText = False
Orientation = 0
AddIndent = False
ShrinkToFit = False
MergeCells = True
End With
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.LargeScroll Down:=1
Range("AA43").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.LargeScroll ToRight:=-1
Range("E2:M2").Select
ActiveSheet.Paste

etc etc............................

Application.ScreenUpdating = True

End Sub


I get the error on this line:

Range("E2:M2").Select

Any ideas on what I am doing wrong will be greatly appreciated!

Thanks in advance.


--
fundoo_pc
------------------------------------------------------------------------
fundoo_pc's Profile:
http://www.excelforum.com/member.php...o&userid=30535
View this thread: http://www.excelforum.com/showthread...hreadid=501867