• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Merge Excel Worksheets with VBA

You are here: Home / Excel VBA / Merge Excel Worksheets with VBA
Merge Excel Worksheets with VBA
October 1, 2013 by Philip Treacy

The Scenario

Let’s say that you have a lot of sheets in your workbook and you want to merge all the data onto a single worksheet.

If you have your data laid out in the same way on each sheet then this piece of VBA will do the trick for you.Β  Maybe you have sales reports for different regions/products/salespeople on separate sheets, something like this :

Sheet 1

Sheet1 Source Data

Sheet 2

Sheet2 Source Data

The code allows for a header row (which is taken from the first sheet), and just copies the data off the remaining sheets.

Merging the sheets together will give you this :

Merged sheets

Note : I used Excel's RANDBETWEEN function to generate the sales figures, arter than type them out by hand.

The merged data is copied onto a sheet called MergedData.Β  If you want to change this sheet name, just change the value between the double quotes of the MergeSheetName variable in the VBA as shown here :

Change merge sheet name in VBA

When you run the code this is what happens :

  1. A new sheet called MergedData is created (or whatever you want to call it).Β  If this sheet already exists then all data on it is deleted
  2. The header row and data from the first sheet in the workbook is copied to the merged data sheet
  3. The data from all other sheets is copied to the merged data sheet
  4. The header row on the merged results is made BOLD
  5. The columns on the merged results are auto-fitted

What to Expect from the VBA

There are certain things the code does not do and it’s important to understand these so you don’t end up with unexpected results

The range to be copied must be contiguous.Β  I use a VBA property called CurrentRegion which copies a range bounded by blank rows/columns like so :

What CurrentRegion selects

Our active cell is B2 so the range to copy is A2 to F4. CurrentRegion will select the range starting at A1, but I'm resizing the range in VBA to exclude the first row as we've already copied that from the first sheet. We only want the header row once.

Row 6 and Column H are ignored as Row 5 and Column G are blank – these are where CurrentRegion understands the range ends.

You can read Microsoft’s explanation of CurrentRegion here

This code cannot be used on a protected worksheet, CurrentRegion does not support this.

The code pastes values and formatting.Β  So your merged sheet will not contain any formulas

Where’s the Code?

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook and open the VBA editor (ALT+F11) to check out the code and start merging.

What do you think?

If you need a hand adapting this to do something else for you please let me know.

This code was written as a result of a question from one of our students, Anna Reifman, thanks Anna.

I’d like to hear from you if you have a problem you think VBA could fix, or if you have your own solution to merging sheets, or even if you have adapted my code to do something else.

Merge Excel Worksheets with VBA

More Excel VBA Posts

automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA

Static Variables in VBA

Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar

Excel Status Bar

Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA

Excel Progress Bar for VBA

Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba

Error Handling in VBA

Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject

Finding File Meta Data Using FileSystemObject

Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBATag: Excel VBA
Previous Post:Excel Find Partial Text StringsExcel Find Partial Text Strings
Next Post:Excel Average IF Excluding OutliersExcel Average IF Excluding Outliers

Reader Interactions

Comments

  1. Russ De Rosayro

    July 25, 2017 at 10:01 pm

    Hi Phil or Mernda or Catalin
    OK, have tried unsuccessfully to post a query in the General section as per Catalin’s instructions / replies on this page. I need to merge several worksheets in a workbook into one sheet. All my worksheets seem to have hidden or blank rows, with some merged cells spanning two rows, and start in column B, Row 4..This was a result of converting/downloading bank statement data in .xls format from my bank.
    I can use Phil’s vba code for merging worksheets but the merged data fails due to the structure of my worksheets.
    If I delete all the blank columns and rows in each of the worksheets everything works fine but…. I have between 25 and 30 worksheets in each file and I have approx 20 files. Phew!

    Could you please tell me how to post correctly as I am having no luck with posting from this page. Catalin requested I post in the General Section, as per her replies to me on this, but am having no luck so far.

    Thanks
    Russ

    Reply
    • Catalin Bombea

      July 26, 2017 at 3:40 am

      Hi Russ,
      I created a new topic here: merge-several-worksheets-in-a-workbook-into-one-sheet
      The topic is in the Public Forums – For Registered Users
      Anyone who has registered on the site can post in these forums, so make sure you create your account on our forum. Once you registered, you will be able to create topics, upload files. To add a reply to an existing topic, just use the button Add Reply. Of course, you have to be logged in to add replies.
      Catalin

      Reply
  2. Russ De Rosayro

    July 20, 2017 at 3:37 pm

    Hi Philip
    I have previously subscribed to the Dashboard & Pivot Tables courses and used Excel daily with these skills I picked up, but have now retired for over a year.
    The old adage holds true…use it or lose it and I am afraid I have lost some of my Excel VBA skills (not that I had that great a range!).
    I downloaded the code for merging several bank statement worksheets into one sheet in an Excel workbook but find when I run it on my file it causes issues. For starters, I can see that my A column is blank but I can compensate by deleting my A column, since my data starts in the B column and row 2. However, I have at least 20 workbooks I want to run this on and each workbook contains up to 30 sheets so manually deleting every blank column A is quite tedious, even if I select all sheets and do a global delete in my file. Can you please help? I can forward the file but did not see how to attach in this reply.
    Thanks
    Russ

    Reply
    • Catalin Bombea

      July 20, 2017 at 9:26 pm

      Hi Russ,
      You can upload a file on our forum (create a new topic and add as many details you have)
      We’ll help you fix it.
      Catalin

      Reply
      • Russ De Rosayro

        July 20, 2017 at 10:12 pm

        Hi Catalin
        Thanks.
        I have uploaded a sample file after going to the Forum link but am not sure of exactly which forum I joined, so how/where do I see the replies?
        Russ

        Reply
        • Catalin Bombea

          July 21, 2017 at 5:01 am

          Hi Russ,
          Not seeing any topic related to your subject on forum, maybe you did not completed it properly. Can you try again, in General section? You can configure your account to receive emails for subscribed topics, when a new reply is added.
          Catalin

          Reply
          • Russ De Rosayro

            July 23, 2017 at 10:57 pm

            Hi Catalin
            OK, posted my detailed problem(s)/queries in General Section but no reply yet?
            What am I doing wrong?

          • Catalin Bombea

            July 24, 2017 at 2:01 am

            Can you send the link to your new topic? There is no new topic on forum, I believe you did not created one. You will have to try again.
            Catalin

  3. Pad

    September 21, 2016 at 12:25 am

    Hey! Great example!! Could you please help me, I am trying to put a button on the spreadhseet that I can click that would do this, instead of running it from the VBA

    Thanks

    Reply
    • Catalin Bombea

      September 21, 2016 at 5:11 am

      Hi Pad,
      You can simply insert any shapes, then right click the shape and click on Assign Macro, then select your code from the list.
      Or, from Developer tab, Insert>Button, then assign macro.

      Reply
  4. Lynne Polk

    January 28, 2016 at 2:06 am

    How do you get this to work on word documents in a folder. I need to copy all the word documents in a folder to an excel spreadsheet. The word document has a table at the top and free form text at the bottom.

    Thanks,
    Lynne

    Reply
    • Philip Treacy

      January 28, 2016 at 10:22 am

      Hi Lynne,

      Its a bit difficult to say exactly without seeing the Word docs and an example of how you want the Excel sheets to look after importing the Word docs. For example, the ‘free form’ text, do you want that imported into a single cell in the Excel sheet, or do you want each line inserted into a different row? Do you want the text wrapped in the cell(s)? What width do you want the cells?

      If you can open a Helpdesk ticket and attach some of the Word docs, with an example worksheet of how you want them to look after importing into Excel, we can give you an idea of what solution would work best.

      If you don’t have many Word docs you may be better off doing the job by hand rather than writing code.

      Regards

      Phil

      Reply
      • Ozz

        December 22, 2017 at 7:57 pm

        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 A2 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 (A2) and Thema (A3)…..”.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
        • Catalin Bombea

          December 23, 2017 at 2:37 am

          Hi Ozz,
          I think you should open a new topic on our forum, and upload a sample file.
          The following code will merge data in a more flexible way, the columns may not be in the same order as in the first sheet:

          Option Explicit

          Sub MergeSheets()
          ' Author - Philip Treacy
          ' http://www.myonlinetraininghub.com/merge-excel-worksheets-with-vba
          ' Date - 30 Sep 2013
          ' Merge all sheets in a workbook into one summary sheet

          Dim MergeSheet As Worksheet, wSheet As Worksheet, tempSheet As Worksheet
          Dim NumRows As Long, StartRow As Long
          Dim FirstSheet As Boolean
          Dim MergeSheetName As String
          Dim DataItem As Variant, HeadersRow As Range, i As Long, HeaderText As String
          Dim DataDict As Object: Set DataDict = CreateObject("Scripting.Dictionary")
          Dim HeadersDict As Object: Set HeadersDict = CreateObject("Scripting.Dictionary")

          MergeSheetName = "MergedData"

          Application.ScreenUpdating = False

          'Add sheet for merged data if it doesn't exist
          On Error Resume Next
          Set tempSheet = ThisWorkbook.Worksheets(MergeSheetName)
          If tempSheet Is Nothing Then

          Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)).Name = MergeSheetName

          End If

          'Setup
          Set MergeSheet = ThisWorkbook.Worksheets(MergeSheetName)
          MergeSheet.UsedRange.ClearContents
          FirstSheet = True
          StartRow = 2

          'collect data from each sheet

          For Each wSheet In Worksheets
          If wSheet.Name <> MergeSheetName Then
          DataDict.Add Key:=wSheet.Name, Item:=wSheet.Cells(1).CurrentRegion
          End If
          Next wSheet

          'loop through data and write to destination

          For Each DataItem In DataDict.Keys
          If FirstSheet Then
          DataDict(DataItem).Copy Destination:=MergeSheet.Range("A1")
          'load headers position
          For i = 1 To DataDict(DataItem).Rows(1).Cells.Count
          HeadersDict.Add Key:=DataDict(DataItem).Rows(1).Cells(i).Text, Item:=DataDict(DataItem).Rows(1).Cells(i).Column
          Next
          FirstSheet = False
          Else
          StartRow = MergeSheet.Range("A" & MergeSheet.Rows.Count).End(xlUp).Row + 1
          'paste data based on each header position
          For i = 1 To DataDict(DataItem).Rows(1).Cells.Count
          ' if we have this header in first sheet, copy the data
          HeaderText = DataDict(DataItem).Rows(1).Cells(i).Text
          If HeadersDict.Exists(HeaderText) Then
          DataDict(DataItem).Columns(i).Offset(1, 0).Resize(DataDict(DataItem).Rows.Count - 1, 1).Copy _
          Destination:=MergeSheet.Cells(StartRow, HeadersDict(HeaderText))
          End If
          Next

          End If
          Next DataItem

          'Tidy Up
          MergeSheet.UsedRange.Rows(1).Font.Bold = True
          MergeSheet.UsedRange.Cells.Columns.AutoFit
          Application.CutCopyMode = False
          Application.ScreenUpdating = True
          Application.Goto MergeSheet.Range("A1")
          Set MergeSheet = Nothing
          Set tempSheet = Nothing
          Set DataDict = Nothing
          Set HeadersDict = Nothing
          End Sub

          You can use the Worksheet_Activate event, whenever you open the Merged Data sheet, the code that collects data should be called.
          See you on forum, with a sample file.
          Cheers,
          Catalin

          Reply
  5. Lynne Polk

    January 28, 2016 at 2:02 am

    This is great! Thanks for sharing.

    Reply
    • Philip Treacy

      January 28, 2016 at 9:48 am

      Thanks πŸ™‚

      Phil

      Reply
  6. Kitty

    October 10, 2015 at 6:08 am

    OMG this made my life so much easier. Thank you!!

    Reply
    • Philip Treacy

      October 10, 2015 at 9:45 am

      Glad it was useful πŸ™‚

      Regards

      Phil

      Reply
  7. David

    January 22, 2015 at 8:08 am

    Beautiful! What would you need to change to have it ignore the first sheet and continue as normal. In other words, I have a “Raw Data” sheet that I’m parsing bits of to Sheet2, Sheet3, etc… then I would like to have those Sheets2, 3, etc. re-combined into the “Merged Data” sheet excluding the “Raw Data” sheet.

    Does that make sense? Thanks for the great walk-through!

    Reply
    • Catalin Bombea

      January 22, 2015 at 7:52 pm

      Hi David,
      You can set a list of sheets to avoid… like this:
      (in this example, the code will avoid Sheet1 and Sheet2)
      For each Wks in Thisworkbook.Worksheets
      If Instr("Sheet1, Sheet2",Wks.Name)=0 Then
      'then this sheet is not in NoSheetsList, can be processed...
      'add here your code
      End If
      Next Wks

      Catalin

      Reply
      • Dominic

        April 27, 2016 at 9:59 am

        Thanks for sharing the vba (downloaded file) to merge the worksheets, it works a treat. But I would also like to incorporate the additional vba to avoid a set of sheets (ie Sheets 1 and Sheets 2 etc). Could you provide the complete vba needed to incorporate the vba in the downloaded file with the code provided in the response to David’s query? I am a novice with excel vba and would like to simply paste the complete vba code into my module?
        Thanks in advance

        Reply
        • Catalin Bombea

          April 27, 2016 at 1:57 pm

          Hi Dominic,
          You will see that it’s a very simple change to make.
          In the downloaded workbook, in the vb module, look at line 35:
          For Each wSheet In Worksheets
          Add a new line below that line:
          If Instr("Sheet1, Sheet2",WSheet.Name)=0 Then

          You have to edit the string “Sheet1, Sheet2” to type the names of the sheets you want to avoid.

          Now, look at row 69: (in the original code, now you have added a new line, it should be in row 70)
          Next wSheet

          Add this text above that line:
          End If

          It should be very intuitive to add new lines of code, it’s similar to editing a text file.
          Let me know if you managed to do it.
          Cheers,
          Catalin

          Reply
          • Dominic

            May 1, 2016 at 10:30 pm

            Hi Catalin,

            Yes, I got it and works a treat, thanks and much appreciated!

            Regards

          • Catalin Bombea

            May 1, 2016 at 11:17 pm

            Great, glad to hear you made it work πŸ™‚

          • Elinor

            June 10, 2017 at 10:18 pm

            good day,
            kindly help me how to use this code, i dont know how to apply this code.
            i dont know how to insert the new code if i want to escape some sheets.
            Because i have three worksheets with the same format and contents and i want to combined those worksheets in just one sheet. so please kindly help me how to do it….
            thank you very much and God bless!

          • Catalin Bombea

            June 11, 2017 at 3:13 pm

            Hi Elinor,
            Please open a new topic on our forum, and upload a sample file, we will help you write the codes into your file. Make sure you don’t upload sensitive data.
            Catalin

  8. Cathy-Lynn Hanson

    January 16, 2014 at 12:41 am

    I am interested in knowing how this is extended to work with a group of workbooks instead of worksheets as Juanito asked. Would you share the steps with me also?

    Thanks

    Reply
    • Catalin Bombea

      January 16, 2014 at 12:17 pm

      Hi Cathy,
      Here is a code, with comments that makes things easier to understand.
      You have to place all workbooks in the same folder, then change the path in code to point to this folder. Also, you can change the range you want to copy.
      Sub MergeAllWorkbooks()
      Dim SummarySheet As Worksheet
      Dim FolderPath As String
      Dim NRow As Long
      Dim FileName As String
      Dim WorkBk As Workbook
      Dim SourceRange As Range
      Dim DestRange As Range

      ' Create a new workbook and set a variable to the first sheet.
      Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

      ' Modify this folder path to point to the files you want to use.
      FolderPath = "C:\Users\Peter\invoices\"

      ' NRow keeps track of where to insert new rows in the destination workbook.
      NRow = 1

      ' Call Dir the first time, pointing it to all Excel files in the folder path.
      FileName = Dir(FolderPath & "*.xl*")

      ' Loop until Dir returns an empty string.
      Do While FileName <> ""
      ' Open a workbook in the folder
      Set WorkBk = Workbooks.Open(FolderPath & FileName)

      ' Set the cell in column A to be the file name.
      SummarySheet.Range("A" & NRow).Value = FileName

      ' Set the source range to be A9 through C9.
      ' Modify this range for your workbooks.
      ' It can span multiple rows.
      Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")

      ' Set the destination range to start at column B and
      ' be the same size as the source range.
      Set DestRange = SummarySheet.Range("B" & NRow)
      Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
      SourceRange.Columns.Count)

      ' Copy over the values from the source to the destination.
      DestRange.Value = SourceRange.Value

      ' Increase NRow so that we know where to copy data next.
      NRow = NRow + DestRange.Rows.Count

      ' Close the source workbook without saving changes.
      WorkBk.Close savechanges:=False

      ' Use Dir to get the next file name.
      FileName = Dir()
      Loop

      ' Call AutoFit on the destination sheet so that all
      ' data is readable.
      SummarySheet.Columns.AutoFit
      End Sub

      Reply
  9. Maneesh Massey

    January 5, 2014 at 11:59 pm

    This looks GREAT !! Only thing I need is to consolidate data using Non-contiguous columns. Can you help me with this please ???
    Thanks in advance.
    ~Regards,
    Maneesh

    Reply
    • Philip Treacy

      January 6, 2014 at 11:32 am

      Hi Maneesh,

      Can you send an example workbook to us with a detailed explanation, via the Help Desk so we can see exactly what it is you want to do.

      Regards

      Phil

      Reply
  10. MF

    October 24, 2013 at 11:23 am

    Hi – thanks for sharing the code. I think many people encounter this problem and need the codes.

    Btw, I am thinking about a non-VBA way “Data Consolidate (sum)” for those who are not comfortable with VBA to achieve the above (provided that there is no duplication of name in different worksheets; and the table structure is just simple as that). I try that and it works.

    Do you think it’s a good alternative?

    Reply
    • Mynda Treacy

      October 24, 2013 at 12:59 pm

      Hi MF,

      The Consolidate tool is occasionally useful, but more often than not I find it doesn’t do exactly what I want. For example, if more than one column contains text it doesn’t bring all of the text in – only the top row and left column.

      It’s a shame because it appears to do so much but in reality it does so little!

      Cheers,

      Mynda.

      Reply
      • MF

        October 24, 2013 at 5:10 pm

        Hi Mynda,
        I agree with you. It “looks” great however there is not many real-life application.
        One way to solve the problem of more than one columns is to concatenate all columns before consolidation, then “text to columns” after the consolidation. BUT This is too troublesome and doesn’t sound like a good solution.
        I am just wondering if there is any real-life application for the “Consolidate” as far as i know not many people talk about it.
        Cheers,
        MF πŸ™‚

        Reply
        • Mynda Treacy

          October 24, 2013 at 9:12 pm

          Hi MF,

          I think the clue is in the fact that not many people talk about it…that’s because it’s farily useless in the real world. Although your workaround is good for those who prefer not to use VBA….although I’m not sure it’d be much faster than copy and paste.

          Kind regards,

          Mynda.

          Reply
  11. juanito

    October 20, 2013 at 5:59 am

    Hi – just last week I started looking for ways to do this (turned out to be a lot less elementary than I’d expected), so this has really come in handy. Thanks!

    Could this be extended to work with a group of workbooks instead of worksheets?

    – juanito

    Reply
    • Philip Treacy

      October 22, 2013 at 10:01 am

      Hi Juanito,

      You’re welcome.

      Yes you could extend this to workbooks. Would you want to email the entire workbook(s), or sheets from within those workbooks?

      regards

      Phil

      Reply
  12. Bryan

    October 2, 2013 at 3:24 am

    Looks good.

    The only thing it’s desperately missing is a function that emails the originator of the workbook to chastise them for poor data structuring πŸ˜‰

    Reply
    • Philip Treacy

      October 2, 2013 at 8:05 am

      LOL Bryan πŸ™‚ Making things look pretty isn’t my forte

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.