+ Reply to Thread
Results 1 to 109 of 109

Selecting a Worksheet Range

  1. #1
    Registered User
    Join Date
    08-22-2005
    Posts
    20

    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?

  2. #2
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  3. #3
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  4. #4
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  5. #5
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  6. #6
    Registered User
    Join Date
    08-22-2005
    Posts
    20
    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.

  7. #7
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  8. #8
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  9. #9
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  10. #10
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  11. #11
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  12. #12
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  13. #13
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  14. #14
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  15. #15
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  16. #16
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  17. #17
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  18. #18
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  19. #19
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  20. #20
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  21. #21
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  22. #22
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  23. #23
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  24. #24
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  25. #25
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  26. #26
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  27. #27
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  28. #28
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  29. #29
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  30. #30
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  31. #31
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  32. #32
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  33. #33
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  34. #34
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  35. #35
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  36. #36
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  37. #37
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  38. #38
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  39. #39
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  40. #40
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  41. #41
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  42. #42
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  43. #43
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  44. #44
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  45. #45
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  46. #46
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  47. #47
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  48. #48
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  49. #49
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  50. #50
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  51. #51
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  52. #52
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  53. #53
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  54. #54
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  55. #55
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  56. #56
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  57. #57
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  58. #58
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  59. #59
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  60. #60
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  61. #61
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  62. #62
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  63. #63
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  64. #64
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  65. #65
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  66. #66
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  67. #67
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  68. #68
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  69. #69
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  70. #70
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  71. #71
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  72. #72
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  73. #73
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  74. #74
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  75. #75
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  76. #76
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  77. #77
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  78. #78
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  79. #79
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  80. #80
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  81. #81
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  82. #82
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  83. #83
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  84. #84
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  85. #85
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  86. #86
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  87. #87
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  88. #88
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  89. #89
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  90. #90
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  91. #91
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  92. #92
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  93. #93
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  94. #94
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  95. #95
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  96. #96
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  97. #97
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  98. #98
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  99. #99
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  100. #100
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  101. #101
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  102. #102
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  103. #103
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  104. #104
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    >
    >


  105. #105
    William Horton
    Guest

    RE: Selecting a Worksheet Range

    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
    > >
    > >


  106. #106
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  107. #107
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




  108. #108
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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

    >
    >




  109. #109
    Bernie Deitrick
    Guest

    Re: Selecting a Worksheet Range

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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1