{"id":6187,"date":"2021-07-12T13:56:15","date_gmt":"2021-07-12T13:56:15","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=6187"},"modified":"2024-03-26T10:56:27","modified_gmt":"2024-03-26T10:56:27","slug":"combine-multiple-excel-worksheets-into-one","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/","title":{"rendered":"Combine Multiple Excel Worksheets into One"},"content":{"rendered":"\n<div class=\"wp-block-file\"><a id=\"wp-block-file--media-70d955f6-6899-4779-adb1-fe9fa3dae152\" href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/12\/Combine-Multiple-Excel-Worksheets-into-One.xlsx\">Combine-Multiple-Excel-Worksheets-into-One<\/a><a href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/12\/Combine-Multiple-Excel-Worksheets-into-One.xlsx\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-70d955f6-6899-4779-adb1-fe9fa3dae152\">Download File<\/a><\/div>\n\n\n\n<p>Many Excel users have found themselves wondering how could they possibly combine multiple sheets into one without going into too much trouble.<\/p>\n\n\n\n<p>The usual answer would be to copy and paste the sheets, but this is not exactly such an easy task and takes a lot of manual work.<\/p>\n\n\n\n<p>Luckily Excel, as always, has some helpful tips to work around this issue.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Combine Multiple Worksheets with Consolidate<\/h2>\n\n\n\n<p>The simplest way to combine <a href=\"https:\/\/officetuts.net\/excel\/examples\/make-multiple-copies-of-a-sheet\/\">multiple sheets is with the Excel<\/a> <strong>Consolidate<\/strong> option. Now, for this feature to be useful, a couple of things have to be in order.<\/p>\n\n\n\n<p>For our example, we will suppose that we have three sheets, all with the statistics for several NBA players in major categories (points, rebounds, assists, and turnovers) for three nights: Monday, Wednesday, and Sunday.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"423\" height=\"225\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png\" alt=\"\" class=\"wp-image-6202\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png 423w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53-300x160.png 300w\" sizes=\"(max-width: 423px) 100vw, 423px\" \/><\/figure>\n\n\n\n<p>Now, data for each night is in a separate sheet. To merge them all with <strong>Consolidate<\/strong> option, several things have to be honored:<\/p>\n\n\n\n<ul>\n<li>Every data set has to be in a separate worksheet. This means that you do not want to put any data into your output sheet. The best thing is to create a separate sheet where all of the <a href=\"https:\/\/officetuts.net\/excel\/examples\/merge-and-center-data\/\">merged data<\/a> will reside.<\/li>\n\n\n\n<li>Every sheet has to be similar, i.e. has to have the same layout. Each column needs to have a header, and data in all sheets should be similar<\/li>\n\n\n\n<li>No <a href=\"https:\/\/officetuts.net\/excel\/formulas\/remove-blank-rows\/\">blank rows<\/a> and columns are allowed in the range that we are going to consolidate.<\/li>\n<\/ul>\n\n\n\n<p>We are going to create a new sheet called <strong>\u201cMain File\u201d<\/strong> and then go to <strong><a href=\"https:\/\/officetuts.net\/excel\/examples\/the-data-tab-on-ribbon\/\">Data tab<\/a> &gt;&gt; Data Tools &gt;&gt; Consolidate:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"329\" height=\"89\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-54.png\" alt=\"\" class=\"wp-image-6203\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-54.png 329w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-54-300x81.png 300w\" sizes=\"(max-width: 329px) 100vw, 329px\" \/><\/figure>\n\n\n\n<p>Once we click on it, a window will appear with several fields to populate:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"440\" height=\"315\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-55.png\" alt=\"\" class=\"wp-image-6204\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-55.png 440w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-55-300x215.png 300w\" sizes=\"(max-width: 440px) 100vw, 440px\" \/><\/figure>\n\n\n\n<p>In the function field, we can choose among 11 functions with which we can merge our sheets, among them <strong>Sum<\/strong>, <strong>Count<\/strong>, <strong>Average<\/strong>, <strong>Min<\/strong>, and <strong>Max<\/strong>.<\/p>\n\n\n\n<p>In our example, we will use the <a href=\"https:\/\/officetuts.net\/excel\/training\/adding-numbers-using-the-sum-function\/\">Sum function<\/a>.<\/p>\n\n\n\n<p>In the <strong>Reference field<\/strong>, we click on the <strong>Collapse Dialog icon<\/strong> (a little box at the end of the right) and then select the range of the first sheet that we want to merge. We use this icon since our sheets are located in this workbook. If there were any sheets in the other workbooks, we would use the <strong>Browse button<\/strong> to find them.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"430\" height=\"303\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-56.png\" alt=\"\" class=\"wp-image-6205\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-56.png 430w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-56-300x211.png 300w\" sizes=\"(max-width: 430px) 100vw, 430px\" \/><\/figure>\n\n\n\n<p>We then click on the <strong>Add button<\/strong>.<\/p>\n\n\n\n<p>We repeat these steps for Wednesday and Sunday sheets. Since all of the headers and first column values are the same and we want to use them in the <strong>Main file<\/strong>, we click on the <strong>Top row<\/strong> and <strong>Left column<\/strong> in <strong>Use labels in<\/strong> part of the window.<\/p>\n\n\n\n<p>For the last part, we will click on <strong>Create links<\/strong> to the source data box to update our main file whenever any of the data in our sheets changes.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"436\" height=\"309\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-57.png\" alt=\"\" class=\"wp-image-6206\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-57.png 436w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-57-300x213.png 300w\" sizes=\"(max-width: 436px) 100vw, 436px\" \/><\/figure>\n\n\n\n<p>Once we click <strong>OK<\/strong>, Excel will automatically calculate (sum in our case) all of the statistical numbers for every player for three nights, i.e. from the three sheets:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"425\" height=\"235\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-58.png\" alt=\"\" class=\"wp-image-6207\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-58.png 425w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-58-300x166.png 300w\" sizes=\"(max-width: 425px) 100vw, 425px\" \/><\/figure>\n\n\n\n<p>Since we click on <strong>Create links to source data<\/strong>, Excel created links to our worksheets and the outline that we see above.<\/p>\n\n\n\n<p>We can click on the plus symbol and then on any cell with the value, and we will see the link in the <a href=\"https:\/\/officetuts.net\/excel\/training\/formula-bar\/\">formula bar<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"428\" height=\"246\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-59.png\" alt=\"\" class=\"wp-image-6208\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-59.png 428w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-59-300x172.png 300w\" sizes=\"(max-width: 428px) 100vw, 428px\" \/><\/figure>\n\n\n\n<p>Although <strong>Consolidate<\/strong> option is pretty great, it has its limitations. This option only works with numeric values. Furthermore, it can only manipulate data in a certain way, i.e. with 11 formulas on disposal, being <strong>Sum<\/strong>, <strong>Count<\/strong>, or one of the other ones.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Combine Multiple Worksheets with Vlookup<\/h2>\n\n\n\n<p>One of the ways in which we can combine the data from multiple <a href=\"https:\/\/officetuts.net\/excel\/formulas\/vlookup-from-another-sheet\/\">sheets in the Excel <strong>VLOOKUP<\/strong><\/a> function. This function searches for a certain value in a column so that it could return a value from a different column in the same row.<\/p>\n\n\n\n<p>It has several parameters:<\/p>\n\n\n\n<ul>\n<li><strong>lookup_value<\/strong>. The value that we are looking for.<\/li>\n\n\n\n<li><strong>table_array<\/strong>. Here we select the range of cells from which we want to derive data from.<\/li>\n\n\n\n<li><strong>column_index_num<\/strong>. The sequence number of the column where our results are.<\/li>\n\n\n\n<li><strong>range_lookup (optional)<\/strong>. Matching mode with two values. <strong>TRUE<\/strong> stands for approximate and <strong>FALSE<\/strong> stands for exact.<\/li>\n<\/ul>\n\n\n\n<p>To show <strong>VLOOKUP <\/strong>in our example, we will create a new sheet, simply call it <strong>\u201cVlookup\u201d<\/strong> and <a href=\"https:\/\/officetuts.net\/excel\/examples\/copy-and-paste-columns\/\">paste the first column<\/a>, the one with the player&#8217;s list. Then, we will create three headers: Points on Monday, Points on Wednesday, and Rebounds on Sunday:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"576\" height=\"219\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-60.png\" alt=\"\" class=\"wp-image-6209\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-60.png 576w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-60-300x114.png 300w\" sizes=\"(max-width: 576px) 100vw, 576px\" \/><\/figure>\n\n\n\n<p>In a cell <strong>B2<\/strong>, we will input the following formula:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=VLOOKUP(A2,'Monday '!A:B,2,0)<\/code><\/pre>\n\n\n\n<p>We want to find the statistics for players, so logically, the values that we want to find are going to be a player\u2019s name. These values are located in the first column.<\/p>\n\n\n\n<p>For the next step, we go to the sheet <strong>\u201cMonday\u201d<\/strong>, in which the player\u2019s names are also located in <strong>column A.<\/strong> We have to make sure that the first column in our table_array <a href=\"https:\/\/officetuts.net\/excel\/formulas\/check-if-column-contains-value\/\">contains the value<\/a> from our <strong>lookup_value<\/strong>.<\/p>\n\n\n\n<p><strong>VLOOKUP<\/strong> only functions from left to right. If, for example, our <strong>lookup_value<\/strong> was in the <strong>B column<\/strong> in the <strong>\u201cMonday\u201d<\/strong> sheet and the data for points was in the <strong>A column<\/strong>, we could not use <strong>VLOOKUP<\/strong> to retrieve the results. <strong>VLOOKUP<\/strong> only functions from left to right. If, for example, our <strong>lookup_value<\/strong> is in the <strong>B column<\/strong> in the <strong>\u201cMonday\u201d<\/strong> sheet and the data for points was in the <strong>A column<\/strong>, we could not use <strong>VLOOKUP<\/strong> to retrieve the results.<\/p>\n\n\n\n<p>Points for each player in sheet <strong>Monday<\/strong> are located in the <strong>B column<\/strong>, so we will only have <strong>columns A <\/strong>and <strong>B<\/strong> for our <strong>table_array<\/strong>.<\/p>\n\n\n\n<p>Next, we have to define our <strong>column_index_num<\/strong>. The results that we need are located In the B column, which is the second column in our <strong>table_array<\/strong>, so we will define <strong>2<\/strong> as the <strong>column_index_num<\/strong>.<\/p>\n\n\n\n<p>For our <strong>range_lookup<\/strong>, we will define <strong>0<\/strong>, which stands for false. This means that our lookup range can be in any order. In this case, Excel returns a value from the first value where the <strong>lookup_value<\/strong> is equal to the <strong>range_value<\/strong>. If we have selected <strong>1<\/strong>, or <strong>True<\/strong>, the <strong>lookup_range<\/strong> should be in ascending order from top to bottom. In this case, Excel returns the last row where the range value is less or equal to the <strong>lookup_value<\/strong>.<\/p>\n\n\n\n<p>For our final step, we will copy and paste the formula till the end of our range (<strong>cell B10<\/strong>).<\/p>\n\n\n\n<p>Our table looks like this:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"578\" height=\"223\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-61.png\" alt=\"\" class=\"wp-image-6210\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-61.png 578w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-61-300x116.png 300w\" sizes=\"(max-width: 578px) 100vw, 578px\" \/><\/figure>\n\n\n\n<p>To retrieve the Wednesday results, all we have to do is replace Monday with Wednesday in our formula, since our data is ordered in the same way in every sheet:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=VLOOKUP(A2,Wednesday!A:B,2,0)<\/code><\/pre>\n\n\n\n<p>And our table now looks like this:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"575\" height=\"232\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-62.png\" alt=\"\" class=\"wp-image-6211\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-62.png 575w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-62-300x121.png 300w\" sizes=\"(max-width: 575px) 100vw, 575px\" \/><\/figure>\n\n\n\n<p>Now, for Sunday, we decided to find the rebounds for every player. We will replace Wednesday with Sunday in our formula and we will change the <strong>table_array<\/strong> and <strong>column_index_num<\/strong>. Our formula will be:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=VLOOKUP(A2,Sunday!A:C,3,0)<\/code><\/pre>\n\n\n\n<p>Since the data for rebounds is located in <strong>column C<\/strong> in the Sunday sheet, we have to define our array to be from <strong>column A<\/strong> (where names of the players are found) and <strong>C <\/strong>(where rebounds are found). Our data is in the third column from starting point, so our <strong>column_index_num<\/strong> is <strong>3<\/strong>.<\/p>\n\n\n\n<p>Finally, our table looks like this:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"576\" height=\"226\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-63.png\" alt=\"\" class=\"wp-image-6212\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-63.png 576w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-63-300x118.png 300w\" sizes=\"(max-width: 576px) 100vw, 576px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Combine Multiple Worksheets with VBA<\/h2>\n\n\n\n<p>As always, there is a way in which you could do this using the <strong>VBA code<\/strong>. We will present the code in full here, and explain the code in detail in the text below. To fully understand this code, you need some knowledge about the <strong>For&#8230;Each loop<\/strong>, <strong>IF function<\/strong>, and <strong>variables<\/strong>. We will explain them, but not in detail in this example.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub CombineWorksheetsIntoOne()\n Application.ScreenUpdating = False\n    Dim wb As Workbook\n    Dim ws As Worksheet\n    Dim ms As Worksheet\n    Dim rng As Range\n    Dim colCount As Integer\n    Set wb = ActiveWorkbook\n    Set ms = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))\n    ms.Name = \"Main\"\n    Set ws = wb.Worksheets(1)\n    colCount = ws.Cells(1, 255).End(xlToLeft).Column\n   \n    With ms.Cells(1, 1).Resize(1, colCount)\n        .Value = ws.Cells(1, 1).Resize(1, colCount).Value\n        .Font.Bold = True\n    End With\n    For Each ws In wb.Worksheets\n        If ws.Index = wb.Worksheets.Count Then\n            Exit For\n        End If\n        Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(65536, 1).End(xlUp).Resize(, colCount))\n        ms.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value\n    Next ws\n    ms.Columns.AutoFit\n    Application.ScreenUpdating = True\nEnd Sub\n<\/code><\/pre>\n\n\n\n<p>In the first part of our code, we define that we want our <a href=\"https:\/\/officetuts.net\/excel\/vba\/turn-screen-updating-on-and-off-in-vba\/\">screen updating<\/a> to be off. When <strong>VBA code <\/strong>executes, it can be seen in the background. We do not want this so we type in:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Application.ScreenUpdating = False<\/code><\/pre>\n\n\n\n<p>Next, we have to define all the variables that will be used in our code:<\/p>\n\n\n\n<ul>\n<li><strong>Dim wb As Workbook<\/strong>&#8211; Dim is short of dimension. It is used to define our variable, in this case, \u201cwb\u201d for our workbook. It is important that this is an object variable since workbooks are objects.<\/li>\n\n\n\n<li><strong>Dim ws As Worksheet<\/strong>&#8211; We define a variable for our sheets as well since we are going to loop through them.<\/li>\n\n\n\n<li><strong>Dim ms As Worksheet<\/strong>&#8211; We have to create our main sheet where the data from the rest of the sheets are going to be. For this, we create a <a href=\"https:\/\/officetuts.net\/excel\/training\/variables\/\">variable<\/a> as a placeholder for our sheet.<\/li>\n\n\n\n<li><strong>Dim rng As Range<\/strong>&#8211; When speaking about data, we can observe it as ranges. This is why we created this variable as well.<\/li>\n\n\n\n<li><strong>Dim colCount As Integer<\/strong>&#8211; This variable will count the columns in our tables in all worksheets.<\/li>\n<\/ul>\n\n\n\n<p>After we defined our variables, we can get to work.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Set wb = ActiveWorkbook<\/code><\/pre>\n\n\n\n<p>First, we set our <strong>wb variable<\/strong> to be equal to the active workbook, so we can start working from there.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Set ms = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))\nms.Name = \"Main\"\n<\/code><\/pre>\n\n\n\n<p>This part of the code sets our variable <strong>ms <\/strong>(we said this variable is for our main sheet), and adds it at the end. It clearly states to add worksheets after you count all the worksheets. Next up, we define the name of our sheet, and set it to <strong>\u201cMain\u201d<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Set ws = wb.Worksheets(1)\ncolCount = ws.Cells(1, 255).End(xlToLeft).Column\n<\/code><\/pre>\n\n\n\n<p>In the line of code above, we go to the first <a href=\"https:\/\/officetuts.net\/excel\/training\/worksheets\/\">worksheet<\/a> in our file to get column headers. Then we count the columns in this sheet with <strong>colCount<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>With ms.Cells(1, 1).Resize(1, colCount)\n       .Value = ws.Cells(1, 1).Resize(1, colCount).Value\n       .Font.Bold = True\nEnd With\n<\/code><\/pre>\n\n\n\n<p>Next, we use the <strong>VBA With function<\/strong> to retrieve the columns from our first sheet to the main sheet. No <a href=\"https:\/\/officetuts.net\/excel\/training\/copying-cutting-and-pasting-data\/\">copy and paste<\/a> methods are used and needed in this step. We then <strong>bold<\/strong> these columns.<\/p>\n\n\n\n<p>For the final part, we will use the <strong>For Each loop<\/strong> to go through all of our sheets. We will explain this loop in more detail.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>For Each ws In wb.Worksheets<\/code><\/pre>\n\n\n\n<p>The first part of the loop defines what object are we going to work on. Since we need to loop through all of our worksheets, we define that for each ws (worksheets) in wb.worksheets (in our workbook) we are going to do some action.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>If ws.Index = wb.Worksheets.Count Then\n       Exit For\nEnd If\n<\/code><\/pre>\n\n\n\n<p>This upper part of the code is an <strong>IF function<\/strong> that is nested into our <strong>For Each loop<\/strong>. This part checks whether we have looped through all of the sheets. If the sheet that we are looping is equal to our last sheet (Master sheet) loop stops executing.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(65536, 1).End(xlUp).Resize(, colCount))<\/code><\/pre>\n\n\n\n<p>At the start of the execution, we are on our first sheet. The line of code above sets the range to start from the second row (we do this since we all copied the first row in the Main sheet).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ms.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value<\/code><\/pre>\n\n\n\n<p>This line of code makes sure that we put the ranges that we defined above into our Main sheet.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Next ws<\/code><\/pre>\n\n\n\n<p>This is the final line of code. It is integral to every <strong>For Each loop<\/strong>. It says that we should repeat all of these steps in the loop until the conditions are met (in this case, until we go through all of our sheets but the last one).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ms.Columns.AutoFit<\/code><\/pre>\n\n\n\n<p>We adjust the width of the column in the Main sheet.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Application.ScreenUpdating = True<\/code><\/pre>\n\n\n\n<p>For the last part, we activate the screen updating again.<\/p>\n\n\n\n<p>We will use our code for the three sheets that we had at the beginning. We will create a new Excel file and present the data about the players and their statistics.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"422\" height=\"497\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-64.png\" alt=\"\" class=\"wp-image-6213\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-64.png 422w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-64-255x300.png 255w\" sizes=\"(max-width: 422px) 100vw, 422px\" \/><\/figure>\n\n\n\n<p>We will <a href=\"https:\/\/officetuts.net\/excel\/training\/saving-files\/\">save this Excel file<\/a> as <strong>Macro-Enabled Workbook<\/strong> and then insert our code. Now, when we execute our code, we will get another sheet:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"396\" height=\"498\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-65.png\" alt=\"\" class=\"wp-image-6214\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-65.png 396w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-65-239x300.png 239w\" sizes=\"(max-width: 396px) 100vw, 396px\" \/><\/figure>\n\n\n\n<p>In the <strong>Main sheet<\/strong> that we got, we have column names as in the first sheet, and the list of all players and their statistics for three previous nights.<\/p>\n\n\n\n<p>This code can get some modifications, for example, to create another column and add the name of the original sheet from which the data originates, or any other aspect. But, this code certainly serves as a great starting point.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many Excel users have found themselves wondering how could they possibly combine multiple sheets into one without going into too much trouble. The&#8230;<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Combine Multiple Excel Worksheets into One<\/title>\n<meta name=\"description\" content=\"is tutorial, learn how to combine multiple Excel sheets into one without the hassle. Follow our guide to use the Consolidate option effectively.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Combine Multiple Excel Worksheets into One\" \/>\n<meta property=\"og:description\" content=\"is tutorial, learn how to combine multiple Excel sheets into one without the hassle. Follow our guide to use the Consolidate option effectively.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-07-12T13:56:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-26T10:56:27+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png\" \/>\n<meta name=\"author\" content=\"Harun Spahic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Harun Spahic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/\"},\"author\":{\"name\":\"Harun Spahic\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\"},\"headline\":\"Combine Multiple Excel Worksheets into One\",\"datePublished\":\"2021-07-12T13:56:15+00:00\",\"dateModified\":\"2024-03-26T10:56:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/\"},\"wordCount\":1978,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/\",\"name\":\"Combine Multiple Excel Worksheets into One\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png\",\"datePublished\":\"2021-07-12T13:56:15+00:00\",\"dateModified\":\"2024-03-26T10:56:27+00:00\",\"description\":\"is tutorial, learn how to combine multiple Excel sheets into one without the hassle. Follow our guide to use the Consolidate option effectively.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Combine Multiple Excel Worksheets into One\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\",\"name\":\"Harun Spahic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"caption\":\"Harun Spahic\"},\"sameAs\":[\"http:\/\/human.bsru.ac.th\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Combine Multiple Excel Worksheets into One","description":"is tutorial, learn how to combine multiple Excel sheets into one without the hassle. Follow our guide to use the Consolidate option effectively.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/","og_locale":"en_US","og_type":"article","og_title":"Combine Multiple Excel Worksheets into One","og_description":"is tutorial, learn how to combine multiple Excel sheets into one without the hassle. Follow our guide to use the Consolidate option effectively.","og_url":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/","article_published_time":"2021-07-12T13:56:15+00:00","article_modified_time":"2024-03-26T10:56:27+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png"}],"author":"Harun Spahic","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Harun Spahic","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/"},"author":{"name":"Harun Spahic","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2"},"headline":"Combine Multiple Excel Worksheets into One","datePublished":"2021-07-12T13:56:15+00:00","dateModified":"2024-03-26T10:56:27+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/"},"wordCount":1978,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/","url":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/","name":"Combine Multiple Excel Worksheets into One","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png","datePublished":"2021-07-12T13:56:15+00:00","dateModified":"2024-03-26T10:56:27+00:00","description":"is tutorial, learn how to combine multiple Excel sheets into one without the hassle. Follow our guide to use the Consolidate option effectively.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/07\/word-image-53.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/combine-multiple-excel-worksheets-into-one\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Combine Multiple Excel Worksheets into One"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2","name":"Harun Spahic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","caption":"Harun Spahic"},"sameAs":["http:\/\/human.bsru.ac.th\/"]}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/6187"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=6187"}],"version-history":[{"count":18,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/6187\/revisions"}],"predecessor-version":[{"id":15735,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/6187\/revisions\/15735"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=6187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=6187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=6187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}