Consolidate data in Excel and merge multiple sheets into one worksheet
by | updated on July 30, 2021
The tutorial demonstrates different ways to combine sheets in Excel depending on what result you are after - consolidate data from multiple worksheets, combine several sheets by copying their data, or merge two Excel spreadsheets into one by the key column.
Today we will tackle a problem that many Excel users are struggling with daily - how to merge multiple Excel sheets into one without copying and pasting. The tutorial covers two most common scenarios: consolidating numeric data [sum, count, average, etc.] and merging sheets [i.e. copying data from multiple worksheets into one].
- How to consolidate data in Excel
- How to merge Excel sheets into one
- Combine sheets with Ultimate Suite
- Merge sheets using VBA code
- Combine data from multiple worksheets with PowerQuery
- Merge two Excel sheets into one by the key column
Consolidate data from multiple worksheets in a single worksheet
The quickest way to consolidate data in Excel [located in one workbook or multiple workbooks] is by using the built-in Excel Consolidate feature.
Let's consider the following example. Supposing you have a number of reports from your company regional offices and you want to consolidate those figures into a master worksheet so that you have one summary report with sales totals of all the products.
As you see in the screenshot below, the three worksheets to be consolidated have a similar data structure, but different numbers of rows and columns:
To consolidate the data in a single worksheet, perform the following steps:
- Arrange the source data properly. For the Excel Consolidate feature to work correctly, make sure that:
- Each range [data set] you want to consolidate resides on a separate worksheet. Don't put any data on the sheet where you plan to output the consolidated data.
- Each sheet has the same layout, and each column has a header and contains similar data.
- There are no blank rows or columns within any list.
- Run Excel Consolidate. In the master worksheet, click the upper-left cell where you want the consolidated data to appear, go to the Data tab and click Consolidate.Tip. It's is advisable to consolidate data into an empty sheet. If your master worksheet already has some data, make sure there is enough space [blank rows and columns] to contain the merged data.
- Configure the consolidation settings. The Consolidate dialog windows appears and you do the following:
- In the Function box, select one of the summary functions you want to use to consolidate your data [Count, Average, Max, Min, etc.]. In this example, we select Sum.
- In the Reference box, clicking the Collapse Dialog icon and select the range on the first worksheet. Then click the Add button to have that range added to the All references Repeat this step for all the ranges you want to consolidate.
If one or some of the sheets reside in another workbook, click the Browse bottom to locate the workbook.
- Configure the update settings. In the same Consolidate dialog window, select any of the following options:
- Check the Top row and/or Left column boxes under Use labels if you want the row and/or column labels of the source ranges to be copied to the consolidation.
- Select the Create links to source data box if you want the consolidated data to update automatically whenever the source data changes. In this case, Excel will create links to your source worksheets as well as an outline like in the following screenshot.
If you expand some group [by clicking the plus outline symbol], and then click on the cell with a certain value, a link to the source data will display in the formula bar.
As you see, the Excel Consolidate feature is very helpful to pull together data from several worksheets. However, it does have a few limitations. In particular, it works for numeric values only and it always summarizes those numbers in one way or another [sum, count, average, etc.]
If you want to merge sheets in Excel by copying their data, the consolidation option is not the way to go. To combine just a couple of sheets, you may not need anything else but the good old copy/paste. But if you are to merge tens of sheets, errors with manual copying/pasting are inevitable. In this case, you may want to employ one of the following techniques to automate the merge.
How to merge Excel sheets into one
Overall, there are four ways to merge Excel worksheets into one without copying and pasting:
- Combine sheets with Ultimate Suite
- VB scripts
- Power Query for Excel
- Merge two Excel sheets into one by the key column[s]
How to combine Excel spreadsheets with Ultimate Suite
The built-in Excel Consolidate feature can summarize data from different sheets, but it cannot combine sheets by copying their data. For this, you can use one of the merge & combine tools included with our Ultimate Suite for Excel.
Combine multiple worksheets into one with Copy Sheets
Supposing you have a few spreadsheets that contain information about different products, and now you need to merge these sheets into one summary worksheet, like this:
With the Copy Sheets added to your ribbon, the 3 simple steps is all it takes to merge the selected sheets into one.
- Start the Copy Sheets Wizard.
On the Excel ribbon, go to the Ablebits tab, Merge group, click Copy Sheets, and choose one of the following options:
- Copy sheets in each workbook to one sheet and put the resulting sheets to one workbook.
- Merge the identically named sheets to one.
- Copy the selected sheets to one workbook.
- Combine data from the selected sheets to one sheet.
Since we are looking to combine several sheets by copying their data, we pick the last option:
- Select worksheets and, optionally, ranges to merge.
The Copy Sheets wizard displays a list of all the sheets in all open workbooks. Select the worksheets you want to combine and click Next.
If you don't want to copy the entire content of a certain worksheet, make use of the Collapse Dialog icon to select the desired range as shown in the screenshot below.
In this example, we are merging the first three sheets:
Tip. If the worksheets you want to merge reside in another workbook that is currently closed, click the Add files... button to browse for that workbook. - Choose how to merge sheets.
In this step, you are to configure additional settings so that your worksheets will be combined exactly the way you want.
How to paste the data:
- Paste all - copy all the data [values and formulas]. In most cases, it is the option to choose.
- Paste values only - if you don't want formulas from the original sheets to be pasted into the summary worksheet, select this option.
- Create links to source data - this will inset formulas linking the merged data to the source data. Select this option if you want the merged data to update automatically whenever any of the source data changes. It works similarly to the Create links to source data option of Excel Consolidate.
How to arrange the data:
- Place copied ranges one under another - arrange the copied ranges vertically.
- Place copied ranges side by side - arrange the copied ranges horizontally.
How to copy the data:
- Preserve formatting - self-explanatory and very convenient.
- Separate the copied ranges by a blank row - select this option if you want to add an empty row between data copied from different worksheets.
- Copy tables with their headers. Check this option if you want the table headers to be included in the resulting sheet.
The screenshot below shows the default settings that work just fine for us:
Click the Copy button, and you will have the information from three different sheets merged into one summary worksheet like shown in the beginning of this example.
Other ways to combine sheets in Excel
Apart from the Copy Sheets wizard, the Ultimate Suite for Excel provides a few more merging tools to handle more specific scenarios.
Example 1. Merge Excel sheets with a different order of columns
When you are dealing with the sheets created by different users, the order of columns is often different. How do you handle this? Will you be copying the sheets manually or moving columns in each sheet? Neither! Commit the job to our Combine Sheets wizard:
And the data will be combined perfectly by column headers:
Example 2. Merge specific columns from multiple sheets
If you have really large sheets with tons of different columns, you may want to merge only the most important ones to a summary table. Run the Combine Worksheets wizard and select the relevant columns. Yep, it's that easy!
As the result, only the data from the columns that you selected get into the summary sheet:
These examples have demonstrated only a couple of our merge tools, but there is much more to it! After experimenting a bit, you will see how useful all the features are. The fully functional evaluation version of the Ultimate Suite is available for download at the end of this post.
Merge sheets in Excel using VBA code
If you are a power Excel user and feel comfortable with macros and VBA, you can combine multiple Excel sheets into one by using some VBA script, for example this one.
Please keep in mind that for the VBA code to work correctly, all of the source worksheets must have the same structure, the same column headings and same column order.
Combine data from multiple worksheets with Power Query
Power Query is a very powerful technology to combine and refine data in Excel. At that, it's rather complex and requires a long learning curve. The following tutorial explains the common uses in detail: Combine data from multiple data sources [Power Query].
How to merge two Excel sheets into one by the key column[s]
If you are looking for a quick way to match and merge data from two worksheets, then you can either employ the Excel VLOOKUP function or embrace the Merge Tables Wizard. The latter is a visual user-friendly tool that lets you compare two Excel spreadsheets by a common column[s] and pull matching data from the lookup table. The following screenshot demonstrates one of possible results.
The Merge Tables wizard is also included with the Ultimate Suite for Excel.
This is how you consolidate data and merge sheets in Excel. I hope you will find the information in this short tutorial helpful. Anyway, I thank you for reading and look forward to seeing you on this blog next week!
Available downloads
Ultimate Suite 14-day fully-functional version [.zip file]
You may also be interested in
- How to merge multiple Excel files into one
- How to merge rows in Excel without losing data
- How to merge two columns in Excel without losing data
- CONCATENATE in Excel: combine text strings, cells and columns
Excel: featured articles
- Merge multiple sheets intoone
- Combine Excel files into one
- Compare two files / worksheets
- Merge 2 columns in Excel
- Compare 2 columns in Excel for matches and differences
- How to merge two or more tables in Excel
- CONCATENATE in Excel: combine text strings, cells and columns
- Create calendar in Excel [drop-down and printable]
- 3 ways to remove spaces between words in Excel cells
Table of contents
97 comments to "Consolidate data in Excel and merge multiple sheets into one worksheet"
- Srinivas Gogula says:June 23, 2021 at 5:12 am
Hi Svetlana Cheusheva ,
Your article helped me save many time-consuming processes of consolidating the multiple sheets into a single sheet in the same workbook using the VBA Method.
I am a finance graduate and I sometimes have difficulty with excel formulae whenever I face something difficult in excel I Check your article if there any post you have made relevant to that and that's it my issue will be fixed if I have any post about it and I also study excel formulae using your articles which makes me a better understanding of everything just like index and match, Randomfunction which I used to prepare my schedule to study randomly list goes on.
Thank you so much for your work and passion and have a nice day.
Reply - Harry says:June 3, 2021 at 11:08 pm
When combining Excel spreadsheets, you want the source of the worksheet displayed in a column [e.g. Products1, Products2, Products3] so that you know where each line of the data in the combined worksheet is originated from. I couldn't figure out how to do it. Can the tool handle this basic need?
Reply- Alexander Trifuntov [Ablebits Team] says:June 4, 2021 at 2:57 pm
Hi,
Unfortunately, there is currently no such feature.Reply - Rolf says:September 14, 2021 at 7:46 am
You could do this with PowerQuery.
As a general approach you would start by importing your tab "Products1" into the PowerQuery Editor. Then you'd create a new "Customised column" with e.g. the column title "Origin" and a fix value of "Prod_1".
You repeat the same steps with Products2, Products3 etc. When you then merge your separate tables in PowerQuery, you'll end up with a merged column "Origin" which contains the information of where a given lines originates from.
Reply
- Nadeem ul Hasnain says:January 29, 2021 at 12:22 pm
Hello Dear
I have converted PDF file into Excel but 426 sheets are created in converted excel file. The data in all sheets is unique. How can I merge all the data in a single sheet.Reply- Alexander Trifuntov [Ablebits Team] says:January 29, 2021 at 1:30 pm
Hello!
I'd recommend you to have a look at our Ablebita Data - Copy Sheets tool that can help you to merge all the data in a single sheet.
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.Reply
- David T Ghogomu says:August 12, 2020 at 10:08 am
Hi,
I have downloaded the Ultimate suite but an error occurred trying to install it on my laptop as below:
The installer was interrupted before ablebits Ultimate suite for Microsoft Excel, personal edition could be installed. You need to restart the installer try again.
Click "close" to exit.Looking forward to your earliest reply.
Kind regards
DavidReply- Ekaterina Bespalaya [Ablebits Team] says:August 12, 2020 at 1:13 pm
Hello David,
Thank you for contacting us. Sorry to hear that you are having difficulties with the installation of our product. Can you please specify on which step of the Installation wizard the error occurs and send us its screenshot to ? We'll do our best to help you.
Reply
- Tania Khan says:July 22, 2020 at 12:10 pm
Hey,
So we have 2 excel sheets - one has Customer Details and the second has Sales details.
if i have a common column in the 2 sheets - Customer ID - the two sheets have different columns but have 2 matching columns - customer ID and Name ]
How would I go about merging the 2 sheets?
Regards,
TReply- Alexander Trifuntov [Ablebits Team] says:July 23, 2020 at 7:38 am
Hello Tania!
How to merge two or more tables in Excel read in this article.
We have a tool that can solve your task in a couple of clicks: Ablebits Data - Merge two tables.This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: //www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Reply
- Charles says:May 6, 2020 at 6:10 pm
Is there a way create a single workbook by appending 160 similar single-page workbooks and have each of the appended workbooks be on a different sheet. In other words, all 160 appended workbooks would exist in a single workbook with 160 sheets.
Reply- Alexander Trifuntov [Ablebits Team] says:May 7, 2020 at 1:33 pm
Hello Charles!
Number of Sheets in a workbook limited by available memory [default is 1 sheet]Reply
- Patel Vimal says:April 3, 2020 at 12:34 pm
In this sheet raw open on top side but i want to open raw on bottom side. is that possible
?Reply - Jolene says:March 10, 2020 at 12:17 pm
Hi, I would like to compile multiple data from multiple excel tabs into a table in 1 master worksheet. Is it possible to do that?
Reply- Alexander Trifuntov [Ablebits Team] says:March 23, 2020 at 8:48 am
Hello Jolene,
If I understand your task correctly, you can solve it using our Combine Sheets tool. Please check out the add-in's manual here, I believe it'll be useful.
I hope this will help, otherwise please do not hesitate to contact me anytime.Reply
- Bindu says:February 14, 2020 at 10:19 am
Hi, is it possible to add each sheet name into the consolidated Sheet?
Reply - chandrakant says:December 16, 2019 at 12:27 pm
How to copy data from multiple sheet into one summary
Reply - Ramesh says:December 10, 2019 at 3:48 pm
Table 1:
BOOKING ID CHECK IN CHECK OUT NO OF ROOMS Tariff
AGVK4701 10/19/2019 10/20/2019 3 7674
AIUV9058 10/27/2019 10/28/2019 1 1427
BQMZ7562 10/20/2019 10/21/2019 1 1228
BZWG1548 10/16/2019 10/19/2019 2 8272
CRGT0534 10/19/2019 10/20/2019 1 2155
CRWJ9574 10/24/2019 10/25/2019 1 892MAster Data
Booking ID Guest Reconciliation status Booking Status
ABJC1972 Mr C Santhosh Completed Checked Out
ABTR8361 kuna Anveshkumar Completed Checked Out
ACVM0475 Brahmaiah Manam Completed Checked Out
ACVS5914 Prasanth Completed Checked Out
ADWG1024 Parul Shankhala Completed Checked Out
AENP3549 Parul Shankhala Completed Checked Out
AFEK2374 rakesh Completed Checked Out
AFPN6754 N Completed Checked OutI am looking the ID of the First work sheet with that of MAster sheet using the following formula =VLOOKUP[A2,'Booking Summary HYD770 Sorted'!$A$2:$A$1126,1,FALSE] in one of the column in Table 1. However it is finding the ID's that are available in various rows. However along with the ID I want a particular column from the Master sheet to be copied into Table 1 and in place of '1' I have given 29 as the cell data I want to retrieve before FALSE parameter in the above formula it is giving REF error. How to get it.
Reply - Ann says:December 7, 2019 at 12:38 pm
My husband is trying to merge two work sheets. However when he uses consolidate so information goes missing. How is this resolved?
Reply - Fida says:November 15, 2019 at 2:43 pm
Hi,
I have 48 sheets in a workbook.each sheet has same cells and text values but different numeric values.
For example,
Sheet 1
Date Density Temperature volume
15.11 .789 36c 48970
Sheet 2
Date Density Temperature volume
16.11 .700 46 40000
Summery sheet [Required]
Date Density Temperature volume
15.11 .789 36 48970
16.11 .700 46 40000I want that as I add sheet3 its required entries may shown in summery sheet on next row.and this row's data may dissapear when I delete sheet3.please guide
Reply - Tiffany says:October 29, 2019 at 1:46 pm
Good Morning,
I have two excel workbooks that I am trying to merge together. One is an old one, one is a new one. Both have multiple tabs. Is it possible to merge the data so that it goes into each tab? For instance:
Tab1 [Old] is named: Bay Valley Foods
Tab1 [new] is named: Bay Valley Foods
Tab2 [Old] is named: Ralcorp
Tab2 [New] is named: RalcorpI am trying to combine Tab1 [new] into Tab1[old] and Tab2 [new] into Tab2 [Old].
As this is a work computer, I am also not allowed to download anything including the merge table wizard. So I would need to know if this is possible without that.
Reply - Kaitlin Hartjoy says:July 9, 2019 at 2:46 pm
Hi,
I'd like to automate the process of merging data from two separate sheets. Is there a macro or a VBA script that will allow me to use the Merge Two Tables option to quickly merge data? When I try to record a macro for the Merge Two Tables feature it just shows up blank. Thank you!Reply - Kiran says:July 3, 2019 at 6:39 am
my question is how to combine multiple column data into the single cell? below given data is from multiple columns and i am trying to consolidate all in single cell in sheet2.
For Example:
Description "From Sheet1" - from sheet1
[SNOW:XXXXXXXX [From Shee1] SOLMAN:XXXXX [From Sheet1]]Please advise
Reply - David Nelson says:June 6, 2019 at 7:07 pm
trying to combine 14 sheets together....only 8 columns each, only 40 rows each. Only combines 13 together. No matter which one i start with it always leaves one out????
Reply - Gaurav says:May 24, 2019 at 1:25 pm
I'm trying to consolidate data of my team members using below macros. However, the challenge is, it pulls column names for users who do not have any data yet in their spreadsheets. I have to manually delete the column names. Is there a way that macros can skip spreadsheets which have no data.
Sub SalesConsolidation[]
'*********************************************************************
'This part of the code opens each file and saves it on Sales tab
Application.ScreenUpdating = False
Path = "C:\My Data\Clients\Analysts\"
Filename = Dir[Path & "*.xls"]
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=False
Application.Goto ActiveWorkbook.Sheets["Sales"].Cells[6, 5]
ActiveWorkbook.Save
Workbooks[Filename].Close
Filename = Dir[]
Loop
'**********************************************************************
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject["Scripting.FileSystemObject"]'change folder path of excel files here
Set dirObj = mergeObj.Getfolder["C:\My Data\Clients\Analysts\"]
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open[everyObj]'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range["A2:IV" & Range["A65536"].End[xlUp].Row].Copy
ThisWorkbook.Worksheets["Consolidated_Sales"].Activate'Do not change the following column. It's not the same column as above
Range["A65536"].End[xlUp].Offset[1, 0].PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
Application.ScreenUpdating = True
End SubReply - Pranesh.S says:May 11, 2019 at 10:12 am
How to merge multiple excel sheet into one consolidated data in excel format
Reply - Mohamed Nabil says:May 9, 2019 at 4:12 am
in Combine multiple worksheets into one with Copy Sheets if i a have the same range for all my work sheets can i apply it for them all in the same time or do i have to reselect it for every work sheet
Reply - deepal says:May 8, 2019 at 6:05 pm
i have 4 sheets each sheet have 8 lake[excel capacity is 10 lake]ROWS data. i want combine in 1 excel sheet. Is it possible?
is Yes ..then please guide me..Reply - Melody says:April 25, 2019 at 3:44 am
Hi
How can you do these on Google sheets?Reply- Natalia Sharashova [Ablebits Team] says:April 25, 2019 at 2:43 pm
Hi Melody,
Please have a look at the way our Combine Sheets for Google Sheets works. It is designed to bring data from multiple sheets into one [considering all headers your tables may have, if necessary]. Perhaps, you'll be able to make use of it.Reply
- Liesel says:March 19, 2019 at 7:09 am
Hi, is it possible to add each sheet name into the consolidated Sheet?
Reply - Pk says:March 15, 2019 at 4:09 am
I have a requirement. I have data in sheet 1, Sheet 2 and sheet 3 in a excel. I have around 100 records in each sheets. I want to combine all this data in another sheet, say sheet 4. The order of the record in sheet 4 should be like 1 record from sheet 1, next record should be from sheet 2 and next should be fro sheet 3 and again go to sheet 1 and so on... Note all the column names are the same in all the sheet. Any short cut techniques other than copy paste
Reply- Yeshwanth says:March 21, 2019 at 8:09 pm
Once you install the plugin, then under the plugin/addon use "copy sheets --> Data from the selected sheets --> select row you want to copy from multiple sheets" then set your preferences to create the new sheet[in your case sheet4]. It should work, but you have to give the row range for all the sheets not sure if there any program to run this method. Let me know how it goes, even I'm working to get the data in similar way, will share you if I have anything
Reply
- Yeshwanth says:March 5, 2019 at 11:21 pm
Hi,
I'm using your add-in for the first time. Thanks for it, save lot of time.
But I have an issue when using it, for the first time when I try to consolidate the data[specific row range] from multiple sheets[6 sheets] into one master sheet it worked. But for the second time it won't allow me to specify the data range, is it not letting me because I use trial version? let me know
Thanks,
YeshwanthReply - paramveer says:February 8, 2019 at 9:26 am
How can i Marge all worksheet into a single worksheet within the workbook..
Headers are sameReply- Svetlana Cheusheva [Ablebits Team] says:February 11, 2019 at 8:40 am
Hi Paramveer,
If my understanding of the task is correct, you can use our Combine Sheets tool for this. Here's is a step-by-step example: How to combine Excel worksheets by column headersReply
- Dean Deolitsis says:January 16, 2019 at 3:01 am
Say you have an employee name list with salaries as well. Some employees have now received a raise and their updated salaries are in a new table along with their names. How would I merge the new salaries to replace the old ones?
Reply- Mary Trifuntova [Ablebits Team] says:January 16, 2019 at 12:25 pm
Hello, Dean,
Thank you for your question.If we understand your task correctly, please have a look at our Merge Tables Wizard. It allows you to update the data in the main table with the data from the lookup table based on the key column[s].
You can install a fully functional 7-day trial version of the add-in and see if it works as you need.
Please find the detailed instructions how to work with the tool on its help page.Feel free to contact us again if you have any other questions or need further assistance.
Reply
- IJAS says:January 8, 2019 at 6:19 am
hi, i i have multiple excel sheets.i needs to combine as one sheet.
please help meReply - somya lamba says:December 28, 2018 at 10:49 pm
Hi,
I need to creates a macro that would merge data in two sheets to one. The number of columns and the column headers are the same.
The only catch here is every time we run the macro we will have different number of rows in each of these sheets so we cannot hard code the row numbers in to the VBA code as it will keep changing. I want a general Macro which would paste the data of one sheet after the other sheet into a new sheet.
Reply - Anthony Brigantic says:November 8, 2018 at 3:30 pm
I have 7 worksheets.
Each worksheet has several matching fields but they also have several different fields.
IS there a way to merge them so the similar fields are in the correct column and the different fields are in their own column?
The different fields will have blank cells were the data doesn't pertain. Also the number of rows increases each day in all worksheets.Reply - Austin Anderson says:September 27, 2018 at 12:26 am
I have 14 different spreadsheets and each spreadsheet has some of the same data but they also have different data for some of the columns and rows. For instance one sheet will go to column N while another one will go to column AB. I want to create a master spreadsheet that combines the data of all 14 spreadsheets into one. We support different groups and the main objective is to try and see what groups have what. It will be a very big workbook. Does the consolidate data tool work for this type of functionality?
Reply - Joseph Madden says:September 23, 2018 at 12:29 am
How do I copy all 3 different reports to its own worksheet within a single excel file
Reply- Mary Trifuntova [Ablebits Team] says:September 26, 2018 at 5:03 pm
Hello, Joseph.
Thank you for contacting us and for your question.
Please have a look at our Combine Worksheets Wizard for Excel. It allows you to combine data from multiple sheets into one at a glance.
You can install a fully functional 7-day trial version of the add-in and see if it works for you using this direct download link:
//www.ablebits.com/files/get.php?addin=xl-consolidate-worksheetsFeel free to contact us if you have any questions or need further assistance. We'll be happy to help you!
Reply
- Chatura says:September 5, 2018 at 4:49 am
Sub MergeSheet[]
'Declaring the Variables
Dim LastRow, ShtCnt As Integer
Dim ShtName As String
Dim NewSht As Worksheet'Assinging a Sheet Name by UserInput
ShtName:
ShtName = InputBox["Enter the Sheet Name you want to create", "Merge Sheet", "Master Sheet"]'Count of Total Worksheet in the present workbook
ShtCnt = Sheets.Count'Using For Loop check if the worksheet exists
For i = 1 To ShtCnt
If Sheets[i].Name = ShtName Then
MsgBox "Sheet already Exists", , "Merge Sheet"
GoTo ShtName
End If
Next i'Create a New Sheet
Worksheets.Add.Name = ShtName'Assigning NewSht as Current Sheet
Set NewSht = ActiveSheet'Moving Worksheet to the beginning of this workbook
NewSht.Move before:=Worksheets[1]'Copying all the data to the New Sheet Using For Loop
For i = 2 To ShtCnt + 1'If i=2 Then copy all the data from the second sheet including header.
If i = 2 Then
Sheets[i].UsedRange.Copy NewSht.Cells[1, 1]
Else'If i is grater than 2 then copy all the data excluding Header[1st Row].
Sheets[i].UsedRange.Offset[1, 0].Resize[Sheets[i].UsedRange.Rows.Count - 1, Sheets[i].UsedRange.Columns.Count].Copy NewSht.Cells[LastRow + 1, 1]
End If
LastRow = NewSht.Cells.SpecialCells[xlCellTypeLastCell].Row
Next i'Displaying the Message after copying data successfully
MsgBox "Data has been copied to " & ShtName, , "Merge Sheet"End Sub
Reply- jamshid says:June 23, 2019 at 9:31 am
hi
thank you a lot
kiss youReply - SHANK says:December 8, 2019 at 10:00 pm
LOVE U
Reply - Andrea says:February 20, 2020 at 8:47 pm
Thank you so much!!! Worked perfectly.
Reply - Vikas Singla says:March 27, 2021 at 3:18 pm
ya hoo Great
Done ThanksReply
- Mohammed Shaikh says:August 25, 2018 at 11:27 am
I have 50 excell sheet. With same structure and column. I want to combine them all into one sheet. It should simply copy sheet one after other using vba.
Reply- Mary Trifuntova [Ablebits Team] says:September 26, 2018 at 5:22 pm
Hello, Mohammed.
If we understand your task correctly, our Combine Worksheets Wizard can help you combine multiple sheets into one without copying and pasting your data.
Feel free to install a fully functional 7-day trial version of the add-in to see how works using this direct link:
//www.ablebits.com/files/get.php?addin=xl-consolidate-worksheetsIf you have any questions, please let us know.
Reply
- KAMLAKAR CHAVAN says:May 22, 2018 at 2:37 pm
Hello Sir/Madam,
Kindly help me to fetch/merge data from various sheets {input from various location to same sheet[Left column and top row header are same]} data in to one sheet. For example I have a sheet having 15 columns and 2000 rows data for 15 locations. Each location entered their data into 15 columns. Now I want to merge all the data into my main sheet [15 columns and 2000 rows]. How I can do it fast without copying individually. Please give me solution.
Thanking you,
Kamlakar ChavanReply - Julia Moore says:April 12, 2018 at 10:20 pm
I want to take work sheet from several different files and combine them , but when I Browse and get the file and try to add it "states consolidation reference not valid"
Reply - shiv sharma says:March 19, 2018 at 5:37 am
this is very needfull but i know for consolidation we need took common range of data...
Reply - Joleen says:February 28, 2018 at 4:06 am
I have 6 sheets with each column total of 10 columns representing a location. Each column i will enter a item#. The master sheet i want to be able to enter the item# and it will pull the info from all 6 sheets for that item# and give me the location. I want to try and eliminate having to use CTL+F every single time.
Reply - SITANAGARAJOO says:January 30, 2018 at 11:02 am
I have previously worked in home loan as a loan processing executive. there I have found that 1st work sheet takes input 2nd worksheet process and 3rd worksheet is output .
1st worksheet takes input when I click on finish button on 1st sheet it directly goes to 3rd sheet which is only for printout. It doesnot show 2nd sheet it directly jumps to 3rd sheet.
I want to create one of this with a little change here, in 1st sheet i want to input or insert images along with data [ alpha and numberic data].
can anyone guide me or help me how to do with the excel or something else.
thanking you
Reply - Ozz says:December 22, 2017 at 10:37 am
Hi everyone,
First of all I have to tell that I have no experience with Macro [VBA Codes]. However what I need is related to this. Maybe you guys could help me with it.
I have a workbook and in this workbook there are 10 worksheets. The first 9 Sheets have the same order of the coloumns of titles and in these columns there are names, dates, percentages of Project Status, comments to Projects etc.. As I said the columns have the same order just the name of the worksheets [for different Teams in the Organisation] are different.
In Addition to this I have to merge all the worksheets and have them in another sheet which is called "Übersicht" [Overview]. However there is a different column in the sheet and it's between "Nr." and "Thema" columns [which are in A1 and B1 in all the 9 Sheets] and this different column called "Kategorie" [in A2 in Übersicht-Overwiev sheet]. As this column is between These the order is like this "Nr. [A1], Kategorie [B1] and Thema [C1].....".So this category column [Kategorie] should be empty except this all the Information should be merged into this sheet. And also when there is a Change or update in any worksheet, the Information in "Übersicht" [Overview] sheet needs to update by itself. How can I do this?
P.S.: Every sheet has different filled rows, some 30, some 13, some 5 etc. And the Teams which are responsible for the Sheets can add or delete some rows [in each row there is different Information for different Projects]. This also means the number of rows can increase or decrease.
I hope I explained it well. Thanks a lot in advance!
I wish you merry Christmas and a happy new year!
oduff
Reply- Gennady Terekhov [Ablebits Team] says:March 6, 2018 at 3:41 pm
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area [VBA-related questions], I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
Reply
- James Afful says:July 19, 2017 at 3:29 pm
How to use excel to compute variance[thus the differences ] for or among three figures e.g 2,000, 2,400 and 1,900 and report the result in a percentage [e.g 19%] and then compare it with a benchmark figure [e.g
Chủ Đề