{"id":2580,"date":"2015-05-19T12:04:48","date_gmt":"2015-05-19T12:04:48","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2580"},"modified":"2023-12-31T00:06:54","modified_gmt":"2023-12-31T00:06:54","slug":"loop-through-excel-worksheets-and-workbooks","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/loop-through-excel-worksheets-and-workbooks\/","title":{"rendered":"Loop Through Excel Worksheets and Workbooks"},"content":{"rendered":"

Using loops to easily make changes across multiple worksheets<\/h2>\n

Loops are one of the key tools in Excel VBA<\/a> when we need to perform tasks through a number of objects (cells, worksheets, charts, workbooks etc.)\u00a0. \u00a0Here we will look at how to loop through Excel worksheets and workbooks.<\/p>\n

Loop Through Excel Worksheets<\/h2>\n

\"Loop<\/a><\/p>\n

Below you will find three examples using different loops but all three will perform exactly the same task.<\/p>\n

The For Each loop<\/h2>\n
    \n
  1. An object variable (sh) is used and declared as Worksheet to tell Excel that we want store worksheets (the address) in the memory \u00a0of our computer (Dim sh As Worksheet).<\/li>\n
  2. The\u00a0For Each\u00a0<\/strong>loop will loop through each worksheet in the active workbook (For Each sh In ActiveWorkbook.Sheets).<\/li>\n
  3. The code will add 500 in A1 in all sheets in the active workbook.<\/li>\n<\/ol>\n

    Sub LoopSheets()
    \nDim sh As Worksheet
    \nFor Each sh In ActiveWorkbook.Sheets
    \nsh.Range(“A1”).Value = 500
    \nNext sh
    \nEnd Sub<\/p>\n

    The For Next loop<\/h2>\n
      \n
    1. A data variable is used to store a whole number (integer) in the computer’s memory (Dim iCounter As Integer).<\/li>\n
    2. The\u00a0For Next\u00a0<\/strong>loop is used to loop through all sheets in the active workbook but the loop needs to know how many worksheets there is in the active workbook (ActiveWorkbook.Sheets.Count).<\/li>\n
    3. The iCounter variable is used to move through the worksheets and the value 500 is entered in A1 in all worksheets in the active workbook (Sheets(iCounter).Range(“A1”).Value =\u00a0500).<\/li>\n<\/ol>\n

      Sub LoopSheetst2()
      \nDim iCounter As Integer
      \nFor iCounter = 1 To ActiveWorkbook.Sheets.Count
      \nSheets(iCounter).Range(“A1”).Value =\u00a0500
      \nNext iCounter
      \nEnd Sub<\/p>\n

      The Do loop<\/h2>\n
        \n
      1. A data variable is used to store a whole number (integer) in the computer’s memory (Dim iCounter As Integer).<\/li>\n
      2. 1 is stored in the iCounter variable (iCounter = 1).<\/li>\n
      3. A\u00a0Do Until\u00a0<\/strong>loop is used to run until criteria is met in this example until the value in the variable iCounter is total number of worksheets in the active workbook plus one (Do Until iCounter = ActiveWorkbook.Worksheets.Count + 1).<\/li>\n<\/ol>\n

        Sub LoopSheets3()
        \nDim iCounter As Integer
        \niCounter = 1
        \nDo Until iCounter = ActiveWorkbook.Worksheets.Count + 1
        \nSheets(iCounter).Range(“A!”).Value = 500
        \niCounter = iCounter + 1
        \nLoop
        \nEnd Sub<\/p>\n

        Loop Workbooks<\/h2>\n

        \"loop<\/a><\/p>\n

        Below you will find three examples using different loops but all three will perform exactly the same task but this time the loops will loop through workbooks.<\/p>\n

        The For Each loop<\/h2>\n
          \n
        1. An object variable (wBook) is used and declared as Workbook to tell Excel that we want store workbooks (the address)\u00a0in the memory of our computer (Dim WBook As Workbook).<\/li>\n
        2. The\u00a0For Each\u00a0<\/strong>loop will loop through each open workbook\u00a0 (For Each wBook\u00a0In Workbooks).<\/li>\n
        3. The code will add 2\u00a0in A2 in sheet 1 in all open workbooks.<\/li>\n<\/ol>\n

          Sub LoopWorkBooks()
          \nDim WBook As Workbook
          \nFor Each WBook In Workbooks
          \nWBook.Sheets(1).Range(“A2”).Value = 2
          \nNext WBook
          \nEnd Sub<\/p>\n

          The For Next loop<\/h2>\n
            \n
          1. A data variable is used to store a whole number (integer) in the computer’s memory (Dim iWB As Integer).<\/li>\n
          2. The\u00a0For Next\u00a0<\/strong>loop is used to loop through all open workbooks\u00a0but the loop needs to know how many open workbooks we have\u00a0(Workbooks.Count).<\/li>\n
          3. The iWB variable is used to move through the open workbooks and the value 2\u00a0is entered in A2\u00a0in sheet 1 in all open workbooks (Workbooks(iWB).Sheets(1).Range(“A2”).Value =\u00a02).<\/li>\n<\/ol>\n

            Sub LoopWorkBooks2()
            \nDim iWB As Integer
            \nFor iWB = 1 To Workbooks.Count
            \nWorkbooks(iWB).Sheets(1).Range(“A2”).Value =\u00a02
            \nNext iWB
            \nEnd Sub<\/p>\n

            The Do loop<\/h2>\n
              \n
            1. A data variable is used to store a whole number (integer) in the computer’s memory (Dim iCounter As Integer).<\/li>\n
            2. 1 is stored in the iCounter variable (iCounter = 1).<\/li>\n
            3. A\u00a0Do Until\u00a0<\/strong>loop is used to run until the criteria is met in this example until the value in the variable iCounter is total number of open workbooks\u00a0plus one (Do Until iCounter = Workbooks.Count + 1).<\/li>\n<\/ol>\n

              Sub LoopWorkBooks3()
              \nDim iCounter As Integer
              \niCounter = 1
              \nDo Until iCounter = Workbooks.Count + 1
              \nWorkbooks(iCounter).Sheets(1).Range(“A2”).Value = 2
              \niCounter = iCounter + 1
              \nLoop
              \nEnd Sub<\/p>\n

              Loop workbooks & worksheets<\/h2>\n

              \"loop<\/a><\/p>\n

              \"loop<\/a><\/p>\n

               <\/p>\n

              x<\/p>\n

              <\/h2>\n

              In the examples below nested loops are looping through workbooks and worksheets and again the\u00a0For Each<\/strong>,\u00a0For Next\u00a0<\/strong>and the\u00a0Do<\/strong> loop are used to do the job.<\/p>\n

              The For Each loop<\/h2>\n

              Exactly as in the examples above in this post variables are used to store the address of the workbooks and worksheets in the computers memory (Dim WBook As Workbook &\u00a0Dim sh As Worksheet). A For Each\u00a0<\/b>loop is used to run through the workbooks and one to run through the worksheets.<\/p>\n

              Sub\u00a0LoopWorkBookSheets()
              \nDim WBook As Workbook
              \nDim sh As Worksheet
              \nFor Each WBook In Workbooks
              \nFor Each sh In WBook.Worksheets
              \nsh.Range(“a1”) = 2
              \nNext sh
              \nNext WBook
              \nEnd Sub<\/p>\n

              The For Next loop<\/h2>\n

              Two\u00a0For Next\u00a0<\/strong>loops are needed to run through all worksheets in all open workbooks. Two variables are used (counter variables) to loop one workbook at the time and one worksheet.<\/p>\n

              Sub LoopWorkBookSheets2()
              \nDim iWB As Integer
              \nDim iCounter As Integer
              \nFor iWB = 1 To Workbooks.Count
              \nFor iCounter = 1 To Workbooks(iWB).Sheets.Count
              \nWorkbooks(iWB).Sheets(iCounter).Range(“b1”).Value = 450
              \nNext iCounter
              \nNext iWB
              \nEnd Sub<\/p>\n

              The Do loop<\/h2>\n

              It takes more coding to run through all worksheets in all open workbooks by using the\u00a0Do<\/strong> loop. Again two loops are needed one for the workbooks and one for the worksheets.<\/p>\n

              Sub LoopWorkBookSheets3()
              \nDim iWorkBookCounter As Integer
              \nDim iSheetCounter As Integer
              \niWorkBookCounter = 1
              \niSheetCounter = 1
              \nDo Until iWorkBookCounter = Workbooks.Count + 1
              \nDo Until iSheetCounter = Workbooks(iWorkBookCounter).Sheets.Count + 1
              \nWorkbooks(iWorkBookCounter).Sheets(iSheetCounter).Range(“c1”).Value = 5
              \niSheetCounter = iSheetCounter + 1
              \nLoop
              \niWorkBookCounter = iWorkBookCounter + 1
              \niSheetCounter = 1
              \nLoop
              \nEnd Sub<\/p>\n

              Some people\u00a0prefer to use the\u00a0For Each\u00a0<\/strong>loop for a couple of reasons. The\u00a0For Each\u00a0<\/strong>loop is a faster loop and normally you need less coding.<\/p>\n

              Good luck with your loops!<\/p>\n

              Excel course is one of our Microsoft Office 365 training<\/a> courses.<\/span><\/p>\n

              Useful Resources<\/h3>\n

              Macro to Loop Through All Worksheets in a Workbook<\/a><\/p>\n

              Excel VBA 2013: Track Changes With the Inquire Add-In<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

              Using loops to easily make changes across multiple worksheets Loops are one of the key tools in Excel VBA when we need to perform tasks through a number of objects (cells, worksheets, charts, workbooks etc.)\u00a0. \u00a0Here we will look at how to loop through Excel worksheets and workbooks. Loop Through Excel Worksheets Below you will […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4,19],"tags":[342,343,347],"class_list":["post-2580","post","type-post","status-publish","format-standard","hentry","category-excel-training","category-vba-training","tag-loop-excel-worksheets","tag-loop-through-all-excel-files","tag-macro-to-loop-through-all-spreadsheets"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2580","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2580"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2580\/revisions"}],"predecessor-version":[{"id":3735,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2580\/revisions\/3735"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2580"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2580"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2580"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}