Selecting a Worksheet Range

[Coolboy55]

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?

 

[Bernie Deitrick]

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

 

[Guest]

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

 

[Guest]

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

 

[Bernie Deitrick]

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

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]

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.

 

[Bernie Deitrick]

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