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.

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

> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>