Method 'Range' of object '_Global' failed

[fundoo_pc]

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.

 

[Jim Cone]

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



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.

 

[George Nicholson]

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,