Announcement

Collapse
No announcement yet.

For Each Worksheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 00:36.

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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.com ==> English articles ==> Excel memes

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

        Comment


        • #5
          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

          Comment


          • #6
            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

            New users should read the Forum Rules before posting

            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.

            Comment


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

              Comment


              • #8
                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

                Comment


                • #9
                  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, 02:56.
                  Hope that Helps

                  Roy

                  New users should read the Forum Rules before posting

                  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.

                  Comment


                  • #10
                    Re: For Each Worksheet

                    Shouldn't
                    Code:
                    Dim ws As Workbooks
                    be
                    Code:
                    Dim ws As Worksheet
                    ?
                    Entia non sunt multiplicanda sine necessitate.

                    Comment


                    • #11
                      Re: For Each Worksheet

                      Why bother calling the sub?

                      Why not just incorporate the code into the loop and reference the worksheets.
                      Code:
                      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
                      Dim ws As Worksheet
                      Dim a As Range
                      
                          For Each ws In ActiveWorkbook.Worksheets
                              With ws
                                  For Each a In ws.Range("g6", ws.Range("g65536").End(xlUp))
                                      With a
                                          If .Offset(0, -3).Value = "Complete" Then
                                              .Copy
                                              .PasteSpecial Paste:=xlValues
                                      
                                          End If
                                      End With
                                  Next a
                              
                              End With
                          Next ws
                          
                          Application.CutCopyMode = False
                          
                      End Sub
                      Boo!

                      Comment


                      • #12
                        Re: For Each Worksheet

                        are you just converting any formula into values for column G?

                        Comment


                        • #13
                          Re: For Each Worksheet

                          hey all

                          sorry for the delayed response, i had to run out for a bit. Thanks again to everyone for helping me out.

                          royuk, i tried the exact code you posted. the sub was in a separate module as u had stated, not in the workbook page, and i got an error "runtime error 424, "Object required". The following line was highlighted when i clicked debug:


                          Code:
                          For Each a In ws.Range("g6", ws.Range("g65536").End(xlUp))
                          norie, the code u posted works great


                          zimitry, that is right, i am just trying to replace the now() function in excel with a date value. The file is an activity calendar. When someone selects "complete" in the status column of a specific activity, i wanted a date stamp in the "date completed" column.

                          Thanks again everyone, i hope i answered everyones questions. I tried attaching the file but it is too large, i can email it to anyone is interested.

                          Much love!

                          -shaun

                          Comment

                          Working...
                          X