Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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:

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

    VB:
     
    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,063

    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:

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

    VB:
    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
    18,429

    Re: For Each Worksheet

    If you are going to use ActiveSheet in your code, you need to select the worksheet. Try this instead
    VB:
    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

    VB:
    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
    18,429

    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
    VB:
    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,323

    Re: For Each Worksheet

    Shouldn't
    VB:
    Dim ws As Workbooks 
    
    
    be
    VB:
    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