Selecting a Worksheet Range
 

This is my code:

-Sheet4.Range("A3", Cells(l_LastRow, l_LastColumn)).Select-

I get the error message: "Method 'Range' of object '_Worksheet'
failed"

It's a runtime error 1004.

I get this error whenever the active sheet is any sheet besides
Sheet4, and using Worksheets("SheetName") in place of Sheet4 doesn't
work either, instead getting an "Application-defined or object-defined
error."

What's going on?


--
Coolboy55
------------------------------------------------------------------------
Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
View this thread: http://www.excelforum.com/showthread...hreadid=397878

Coolboy55,

You can't select a range on an inactive worksheet. Since you rarely need to select, change your
code to get rid of the .Select commands, or change the code to select sheet4 first.

HTH,
Bernie
MS Excel MVP


"Coolboy55" wrote in message
...

This is my code:

-Sheet4.Range("A3", Cells(l_LastRow, l_LastColumn)).Select-

I get the error message: "Method 'Range' of object '_Worksheet'
failed"

It's a runtime error 1004.

I get this error whenever the active sheet is any sheet besides
Sheet4, and using Worksheets("SheetName") in place of Sheet4 doesn't
work either, instead getting an "Application-defined or object-defined
error."

What's going on?


--
Coolboy55
------------------------------------------------------------------------
Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
View this thread: http://www.excelforum.com/showthread...hreadid=397878

You may have to reference the proper sheet again just before Cells(l_Last.....
Try Sheet4.Cells(l_La....
and see if that works.

Bill Horton

"Coolboy55" wrote:


This is my code:

-Sheet4.Range("A3", Cells(l_LastRow, l_LastColumn)).Select-

I get the error message: "Method 'Range' of object '_Worksheet'
failed"

It's a runtime error 1004.

I get this error whenever the active sheet is any sheet besides
Sheet4, and using Worksheets("SheetName") in place of Sheet4 doesn't
work either, instead getting an "Application-defined or object-defined
error."

What's going on?


--
Coolboy55
------------------------------------------------------------------------
Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
View this thread: http://www.excelforum.com/showthread...hreadid=397878

Sorry....Check that last reply. You have to activate the sheet before you
can select.

"William Horton" wrote:

You may have to reference the proper sheet again just before Cells(l_Last.....
Try Sheet4.Cells(l_La....
and see if that works.

Bill Horton

"Coolboy55" wrote:


This is my code:

-Sheet4.Range("A3", Cells(l_LastRow, l_LastColumn)).Select-

I get the error message: "Method 'Range' of object '_Worksheet'
failed"

It's a runtime error 1004.

I get this error whenever the active sheet is any sheet besides
Sheet4, and using Worksheets("SheetName") in place of Sheet4 doesn't
work either, instead getting an "Application-defined or object-defined
error."

What's going on?


--
Coolboy55
------------------------------------------------------------------------
Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
View this thread: http://www.excelforum.com/showthread...hreadid=397878

Coolboy55,

I should also have mentioned that when you get rid of your select statements, you need to pay
attention to your range objects, since an unqualified range object defaults to the active sheet.
The range in your statement:

Sheet4.Range("A3", Cells(l_LastRow, l_LastColumn)).Select

would be better written (without the select) as

Sheet4.Range("A3", Sheet4.Cells(l_LastRow, l_LastColumn))

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Coolboy55,

You can't select a range on an inactive worksheet. Since you rarely need to select, change your
code to get rid of the .Select commands, or change the code to select sheet4 first.

HTH,
Bernie
MS Excel MVP


"Coolboy55" wrote in message
...

This is my code:

-Sheet4.Range("A3", Cells(l_LastRow, l_LastColumn)).Select-

I get the error message: "Method 'Range' of object '_Worksheet'
failed"

It's a runtime error 1004.

I get this error whenever the active sheet is any sheet besides
Sheet4, and using Worksheets("SheetName") in place of Sheet4 doesn't
work either, instead getting an "Application-defined or object-defined
error."

What's going on?


--
Coolboy55
------------------------------------------------------------------------
Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
View this thread: http://www.excelforum.com/showthread...hreadid=397878

Thank you Bernie! You have been a great help.

What is the usual method for applying borders and shading to certain
cells if not by selecting? I had the feeling I was doing it the long
way, but it didn't occur to me immediately that there was a better way.


--
Coolboy55
------------------------------------------------------------------------
Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
View this thread: http://www.excelforum.com/showthread...hreadid=397878

Coolboy55,

See the sample code below.

HTH,
Bernie
MS Excel MVP

Sub Macro2()
With Worksheets("Sheet2").Range("A1:B10")
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End With
End Sub


"Coolboy55" wrote in message
...

Thank you Bernie! You have been a great help.

What is the usual method for applying borders and shading to certain
cells if not by selecting? I had the feeling I was doing it the long
way, but it didn't occur to me immediately that there was a better way.


--
Coolboy55
------------------------------------------------------------------------
Coolboy55's Profile: http://www.excelforum.com/member.php...o&userid=26508
View this thread: http://www.excelforum.com/showthread...hreadid=397878