Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Looping Macro

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Aberdeen, Scotland, UK
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a simple macro (cut, copy, paste etc) that I want to run on every sheet of my workbook, without having to go to each sheet and run it each time.

    How do I get a macro to run it through every sheet?

    [ This Message was edited by: Stringt on 2002-05-02 02:48 ]

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does this help? Insert this in your macro:

    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
    'Your copy paste code
    Next sht



  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Stringt.

    Why dont you just copy the macro into your personal.xls folder and have excel open it everytime you open a new workbook.If you dont know where to place this email me at garethl@ij.co.za



  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Aberdeen, Scotland, UK
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code does not seem to work. It just does the current sheet you are in then stops... any ideas?

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try a couple other options, depending maybe on what you are trying to do:

    ''''''''''''''''
    for x = 1 to sheets.count
    sheets(x).Select
    'Your code
    next x
    ''''''''''''''''

    Or this, posted on this board sometime ago:

    ShCount = Application.Sheets.Count
    Sheets(1).Select
    For i = 1 To ShCount
    'Your code
    If i = ShCount Then
    End
    ActiveSheet.Next.Select
    End If
    Next i


    Any help?

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Stringt

    You haven't posted your code but, if it refers to any the first sheet or any range name on that sheet, the code will not loop successfully.

    If it does refer to, say, Sheet1 as the object, change it to sh to pick up the current sheet instead.

    e.g. instead of Worksheets("Sheet1").Range("a1").copy

    use

    sh.Range("a1").copy

    Not very good code, but I hope you'll get what I mean.

    Any help?

    Regards

    Robb__

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Aberdeen, Scotland, UK
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    it doesn't seem to recognise the sheets - do i need to replace x's with my sheet names or what?

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
  •