Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: For Each Worksheet

  1. #1
    Join Date
    31st July 2004
    Location
    USA
    Posts
    44

    For Each Worksheet

    Hi all,

    I was wondering how to perform the same macro on every worksheet in the workbook.

    I can write some very basic coding, and I can code it to select every sheet and perform the macro, but i was hoping someone could show me how to simplify the code (write a "for each" loop?)

    wld it be something like:

    Code:
    Sub sheets()
    for each worksheet in workbook
    (Macro)
    next
    end sub
    thanks for the help!

    -shaun
    Last edited by Wigi; March 28th, 2007 at 01:36.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    Re: For Each Worksheet

    yeah something like that would work fine

    Code:
    sub sheets()
    
    For Each Ws In ActiveWorkbook.Worksheets
        
          Call (Macro/Function Name Here)
    
        Next Ws
    
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st July 2004
    Location
    USA
    Posts
    44

    Re: For Each Worksheet

    thanks for the code! my servers shutting down in 2 minutes ill give it a whirl soon as they bring it back up

    -Shaun

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,071

    Re: For Each Worksheet

    Shaun

    use the code tags appropriately. You need a / to end them.
    Regards,

    Wigi

    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  5. #5
    Join Date
    31st July 2004
    Location
    USA
    Posts
    44

    Re: For Each Worksheet

    Hey zimitry!

    It is still only performing the macro in the active sheet. Here is the code:

    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    For Each ws In ActiveWorkbook.Worksheets
    
    Call values
    
    Next ws
    
    End Sub
    maybe it is something in the macro it is calling. here is the code:

    Code:
    Sub values()
    
    Dim a As Range
    
    For Each a In ActiveSheet.Range("g6", ActiveSheet.Range("g65536").End(xlUp))
    If a.Offset(0, -3).Value = "Complete" Then
    a.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Application.CutCopyMode = False
    End If
    
    Next a
    
    End Sub
    thanks for all the help!




    -shaun

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,293

    Re: For Each Worksheet

    If you are going to use ActiveSheet in your code, you need to select the worksheet. Try this instead
    Code:
    Option Explicit
    
    Sub values()
         
        Dim a As Range
         
        For Each a In ws.Range("g6", ws.Range("g65536").End(xlUp))
            If a.Offset(0, -3).Value = "Complete" Then a.Value = a.Value
        Next a
         
    End Sub
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  7. #7
    Join Date
    31st July 2004
    Location
    USA
    Posts
    44

    Re: For Each Worksheet

    i tried the code, but i got an error in the following line

    Code:
     For Each a In ws.Range("g6", ws.Range("g65536").End(xlUp))
    Again, thanks a lot guys, and sorry for my vba ineptitude!

    -shaun

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    31st July 2004
    Location
    USA
    Posts
    44

    Re: For Each Worksheet

    If I add the "Option Explicit" to the top of the code, then I get an error saying the ws variable is not definied. Without that, the Debug error comes up saying "Object Required" in the line posted above

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,293

    Re: For Each Worksheet

    What error are you getting, this works fine for me, your sub values should be in a standard module, not the Workbook
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
       Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
             
            Call values
             
        Next ws
         
    End Sub
     'this should be in a standard module
    Sub values()
        
        Dim a As Range
         
        For Each a In ws.Range("g6", ws.Range("g65536").End(xlUp))
            If a.Offset(0, -3).Value = "Complete" Then a.Value = a.Value
        Next a
         
    End Sub
    Last edited by royUK; March 28th, 2007 at 03:56.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  10. #10
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,324

    Re: For Each Worksheet

    Shouldn't
    Code:
    Dim ws As Workbooks
    be
    Code:
    Dim ws As Worksheet
    ?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: December 19th, 2007, 00:00
  2. Replies: 5
    Last Post: October 3rd, 2006, 15:41
  3. Replies: 33
    Last Post: July 3rd, 2006, 18:28
  4. Import A Single Worksheet From A Multiple Worksheet Workbook Into Access
    By Farrah Farley in forum Excel and/or Access Help
    Replies: 1
    Last Post: February 6th, 2006, 03:13
  5. Replies: 2
    Last Post: May 26th, 2004, 03:11

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