Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Working on unselected sheets

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    England
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How do i get a macro to run on a certain sheet without it being selected.

    Cheers

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Not clear exactly what you are trying to do.
    I think you need to post an example
    Regards
    Derek

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    England
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well what im doing is an A level project using excel. Its going to be used by a systems admin. In this project iv'e got a stock list that is updated every 30 minutes or so by a macro, but to do this the sheet that the macro is working on has to be selected and i thought that it would be anoying if the sheets changed in the middle of the admin doing something so i was wondering if there was a way for the macro to work on the sheet without the sheet being selected.

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-01 02:27, Whitestar wrote:
    Well what im doing is an A level project using excel. Its going to be used by a systems admin. In this project iv'e got a stock list that is updated every 30 minutes or so by a macro, but to do this the sheet that the macro is working on has to be selected and i thought that it would be anoying if the sheets changed in the middle of the admin doing something so i was wondering if there was a way for the macro to work on the sheet without the sheet being selected.

    Use the With Statement..the With statement allows you to perform a series of statements on a specified object without requalifying the name of the object.eg

    With ThisWorkbook.Sheets("Sheet3")
    .Range("A1:A20") = 20
    .Range("C2") = 25
    end with


    HTH

    Ivan

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    England
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thnx for the code, but the sheet still changes when the macro is run.

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-01 06:39, Whitestar wrote:
    Thnx for the code, but the sheet still changes when the macro is run.
    how have you used the code?

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If the ActiveSheet is Sheet2, then this

    ThisWorkbook.Sheets("Sheet3").Range("A1") = 10

    should work WITHOUT changing the active sheet, that is, Sheet2 should be selected.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    New Member
    Join Date
    Feb 2002
    Location
    England
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thats the code im using, but all i get is an error massage.

  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-04 03:44, Whitestar wrote:
    Thats the code im using, but all i get is an error massage.
    What is the code & error msg you get ?

    Ivan

  10. #10
    New Member
    Join Date
    Feb 2002
    Location
    England
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The whole code that i am using is

    ThisWorkbook.Sheets("Stock List").Range("E2").Select
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("C2:D2").Select
    Selection.ClearContents
    Range("E2").Select
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell = ("")
    Selection.Copy
    ActiveCell.Offset(0, -3).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveCell.Offset(0, 1).Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Select
    Selection.ClearContents
    ActiveCell.Offset(1, 1).Select
    Loop

    End Sub

    and the error msg is

    Run-time error 1004
    Select method of range class failed.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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