fbpx
Search
Close this search box.

Consolidate data from different excel files (VBA)

Share

Facebook
Twitter
LinkedIn

This is a guest post by Vijay, our in-house VBA Expert.

Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.

Consolidate Data Demo

First, lets take a look at the consolidate data VBA code.

Consolidate data from different excel files

Consolidating Data from different Excel files – the setup

There is one master file (or sheet) which needs to be consolidated by pulling data from multiple source files containing raw data (having the same data structure).

Lets try to make a generic consolidation macro so that we can use this almost anywhere.

We start of by creating a simple table on our sheet, we will call this List.

Definition List

 

  • On this table essentially we are defining everything that our VBA code needs to know to copy and paste data.
  • We start by telling the name of the Excel workbook and then the complete path (location) of the file.
  • In the next 2 cells we define what are the starting cell and the ending cell that contains our data.
  • Next we are put the name of the worksheet where the data will be pasted. In our example the sheet remains the same however as per your requirements you may put a different sheet name.
  • The last option is to specify where to paste the copied data and we only need to tell the start cell address, the code will automatically select the next empty cell in that column and then paste the data from that point onwards.

 

Let’s understand the code.

Sub GetData()
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As StringstrListSheet = “List”

On Error GoTo ErrH
Sheets(strListSheet).Select
Range(“B2”).Select

‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> “”

strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook

Range(strCopyRange).Select
Selection.Copy

currentWB.Activate
Sheets(strWhereToCopy).Select
lastRow = LastRowInOneColumn(strStartCellColName)
Cells(lastRow + 1, 1).Select

Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub

ErrH:
MsgBox “It seems some file was missing. The data copy operation is not complete.”
Exit Sub
End Sub

We have used the Workbook object to accomplish this task and also the Error handler to trap any errors that may come in case any file is missing.

The current code will display a message box when it is not able to open any file and will stop.

We start by assigning the workbook where we want to consolidate the date to the variable currentWB by using the statement:

Set currentWB = ActiveWorkbook

After this a looping construct has been used to go through all the inputs provided one by one and open the workbooks, it has been assumed these workbooks to contain on the data that we need to copy hence I did not specify the source sheet name, however this can be easily added to this code to add more functionality.

Inside our loop are the 4 variables which are assigned the
1) File name,
2) Copy Range,
3) Where To Copy and
4) Which Column contains the starting cell to paste data.

We open the data workbook by using the Application.Workbooks.Open method.
Once we have our first data workbook open, we assign this to the dataWB variable so that we can easily switch between the two workbooks and close them when the operation has been completed.

Next we select the data that has been assigned to the copy range and copy to the clipboard.

We then switch back to our main workbook and select the sheet where we want to paste the data, I have assigned this to the variable called “strWhereToCopy”. This allows us to paste data onto separate sheets within the same workbook.

I have also made use of UDF (user defined function) to find the last cell in the column that we specify.

Once we have found the last row we then select the next empty cell below that and paste our data then.

Additional things that may be used to enhance this code

1. Since we are using the same instance of Excel we may allow the user to preserve the format of the data being pasted.
2. Allow the user with the option to clear data before new is pasted.

Download Consolidate Data from different files Demo file

Click here to download the workbook.

Please Note: You would need to create the data files on your system, this download only contains the code template to consolidate.

More on VBA & Macros

If you are new to VBA, Excel macros, go thru these links to learn more.

Join our VBA Classes

If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.

Click here to learn more about VBA Classes & join us.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

71 Responses to “Consolidate data from different excel files (VBA)”

  1. Alex says:

    Chandoo, nice article as ever. I use the RDBMerge addin to achieve the same result - lots of great options for merging files/sheets.

    http://www.rondebruin.nl/merge.htm

  2. J says:

    Hi,
    Would it be possible to make it a little bit more flexible and allow the use of wildcard characters in a file name?

  3. Jason H says:

    @J

    You can use the Dir function to enumerate all the files that match a wildcard combination within a specified folder folder.

    Few points you'd have to compensate for in the code provided.

    It assumes that the default sheet that the workbook opens up with contains the data you want ("Copy From Sheet" is not specified).

    The data range is of fixed size, so if your source data can be variable it won't adjust (use of CurrentRegion method is a good way to pick up tables of data)

    My personal preference is to not use the Selection object to manipulate ranges; although I understand this is a VBA beginners guide.

    Regards Jason

    • Suggi says:

      Hi Jason,

      Thanks for sharing the Macro. I am new to Macro and dont know much of the coding.

      You have used Application.Workbooks.Open method for opening the excel. I want to know how can we open file thru "Import Text wizard" method using same macro instead of "Application.Workbooks.Open". There is specif purpose for me to open file thru Import text Wizard.

      I have recorded macro of opening the file thru Import Text Wizard. Code is
      Workbooks.OpenText Filename:="C:\XYZ.xls", Origin:=437, StartRow _
      :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
      , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
      Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
      Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
      16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
      Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _
      29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), _
      Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array( _
      42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _
      Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array( _
      55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), _
      Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array( _
      68, 1)), TrailingMinusNumbers:=True

      Can you help me with the necessary modification to your macro

  4. Elmer says:

    how about copy from different workbooks?

  5. Jake says:

    What if my data already has headers. I would like to import the data into the top row. What would i change to do this?

    Also, a big thank you. This is very helpful.

  6. Mike Ebert says:

    Another add-in that might be relevant here is XLhub (http://www.xlhub.com)--XLhub lets you tie your spreadsheets to a SQL Server database so that you can share data between spreadsheets (which are kept up-to-date across all the files), enable multi-user access, and keep track of different versions. The hardest part is some configuration work with SQL Server--no VBA required. Of course, your Visual Basic solution is probably more appropriate for people who 1) can't install new software (SQL Server) or add-ins or 2) can't pay $99 for a license of XLhub.

    Thanks for the post!

  7. Sophia says:

    Good afternoon,

    Thank you very much for sharing this information! I am very new to VBA and am wondering how to specify the source sheet in the code. My Files have multiple sheets and I only need to consolidate data from one specific sheet of each file to the Master. Can you please let me know how to do this?

    Thank you!
    Sophia

    • Jason H says:

      Sophia,

      Using the existing code above you would need to add a column into the reference table to specify the name and then modify the code to pick up the name of the sheet into a variable such as "strCopySheet". If you added it at right hand end of the list then that would be:

      strCopySheet = ActiveCell.Offset(0, 5).Value

      If you wanted to add it in the middle of the current table then you'd have to change the numbers referring to cells to the right of your new column to move them over 1 also.
       
      Then where the VBA currently says:
       
      Range(strCopyRange).Select

      Change it to:
       
      Sheet(strCopySheet).Range(strCopyRange).Select

      Hope this helps. Maybe when I have a moment I'll write a version that's less reliant on Selection and can do pattern matching etc...

      JH   

      • N00b with Logic says:

        Hello!

        I also needed to select from multiple files and on a a specific datatab.

        For this purpose, I looked for the first time ever into macros and VBA. So I added an extra column (H) with tab names. Next I had a look into VBA, based on suggestion above. With some logic I improved the code, which seems to work just fine:

        ------------------------------------

        Public strFileName As String
        Public currentWB As Workbook
        Public dataWB As Workbook
        Public strCopyRange As String

        Sub GetData()
        Dim strWhereToCopy As String, strStartCellColName As String
        Dim strListSheet As String
        Dim strCopySheet As String

        strListSheet = "List"

        On Error GoTo ErrH
        Sheets(strListSheet).Select
        Range("B2").Select

        'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
        Set currentWB = ActiveWorkbook
        Do While ActiveCell.Value ""

        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
        strWhereToCopy = ActiveCell.Offset(0, 4).Value
        strCopySheet = ActiveCell.Offset(0, 6).Value
        strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
        Set dataWB = ActiveWorkbook

        Sheets(strCopySheet).Select
        Range(strCopyRange).Select
        Selection.Copy

        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select

        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False
        dataWB.Close False
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select
        Loop
        Exit Sub

        ErrH:
        MsgBox "It seems some file was missing. The data copy operation is not complete."
        Exit Sub
        End Sub

        Public Function LastRowInOneColumn(col)
        'Find the last used row in a Column: column A in this example
        Dim lastRow As Long
        With ActiveSheet
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
        End With
        LastRowInOneColumn = lastRow
        End Function

        ----------------------------------------------------------

        An extra idea to make this excel sheet work is to use the =concenate formula to construct the path name.

        Succes!
        N00b with logic

        • Patrick says:

          Hi N00b,

          I have try using your macro to run but it show me "It seems some file was missing. The data copy operation is not complete."

          I check that the file that I want to copy is open and the range has been copied but then it's not pasting to the MasterData?

        • Sandhya says:

          I am very much satisfied with above a select from multiple files into specific tab, but My paste location will start from G258 and so on. can you please suggest me how to change my offset values to paste location.

  8. Bene says:

    Hi

    Thanks for sharing.

    I have used this piece of code successfully to merge over 20 files within my workbook, it is a time saver.
    However I have one questions regarding the Path of the file - the example use the full path but I would like to use the relative path.

    My excel file is called Datacollection.xls and then I have a folder called "reports" that contains my 20 files.
    in the Path column I used \reports\ and it worked but today I added 5 additional files and the relative path does not work for those, but it does for the previous file.

    I'd like to use a relative path for more flexibility. is there any  specific reason why it doesn't work (I am 100% sure that the name of my files are correct and the name of the relative path as well it just won't Open the file - this is where it bugs and says not found) 

    Any idea?
    thanks
     

    • Dan says:

      Hi Rene,

      I need to combine 30 files of the same format with one sheet report into one combined (consolidated)report.
      Can you help me with the macro?

      Yhank you,
      Dan

  9. Ron says:

    I am running into a complie error saying "Sub of Function Not Defined" for the following:

     LastRowInOneColumn

    Could you please help me out!

    Thanks!!1  

  10. Addy says:

    Hi,
    I have having a Data in one Excell sheet and i have to update/Import the data in other excel workbooks. However the cells in which i have to update is not consistent and it will be depending based on the name of the template. So i am looking for a Macro which should identify the cell headers and update the data from the Main Template.
    Could any one help out me please.
     
    Thanks in Advance

  11. vashisth says:

    Hey what if the source data is present in different tabs of the same excel file???
    e.g. for the above example, imagine i have 20 tabs in abc.xlsx file and i need to copy data from all those tabs to some other file.

    Awaiting a helpful reply
    Thanks in advance! 

  12. Sabir says:

    Hi Jason H,

    can you please explain this to me, i have tried placing in the name of the shet using your method but bot ways do not work on my end...

    Can you please assist me with this or if anyone can tell me how to include the name of the actual sheet in the "List" tab and then change the code accordingly to retrieve information specific to a named sheet

  13. Chris says:

    Hi, Jason H. (and/or anyone else who solved this)

    My question is similar to those posed by Sophia and Sabir.  I need to specify the sheet from which the specified ranges will be copied.  I attempted to use the method you suggested, but running the macro then produces the error message.  My code is as follows.  My offsets are different because I have a total of 9 columns (I specified a specific range into which the data should be pasted.  Headings are as follows:

    Item No
    File Name
    Full Path
    Data Range Start Cell
    Data Range End Cell
    Copy to Sheet
    Copy To Location(Start Cell Only)
    Copy To Location(End Cell Only)
    Which Sheet Copy

    Sub GetData()
    Dim strWhereToCopy As String, strStartCellRange As String
    Dim strListSheet As String, strWhichSheetCopy As String
    strListSheet = "List"
    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("B2").Select
    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""
    strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
    strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
    strWhereToCopy = ActiveCell.Offset(0, 4).Value
    strStartCellRange = ActiveCell.Offset(0, 5) & ":" & ActiveCell.Offset(0, 6)
    strWhichSheetCopy = ActiveCell.Offset(0, 7).Value
    Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
    Set dataWB = ActiveWorkbook
    Sheets(strWhichSheetCopy).Range(strCopyRange).Select
    Selection.Copy
    currentWB.Activate
    Sheets(strWhereToCopy).Select
    Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
    Application.CutCopyMode = False
    dataWB.Close False
    Sheets(strListSheet).Select
    ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub
    ErrH:
    MsgBox "It seems one or more files were missing. The data copy operation is not complete."
    Exit Sub
    End Sub

  14. Sabir says:

    HI Chris,

    I got this to ork at my PC at home and it works brilliantly, when i take bring in to work it fails for some reason, just a snippet of my code below, in your code i can see where your error may lie

    strStartCellRange = ActiveCell.Offset(0, 5) & “:” & ActiveCell.Offset(6, 0)
    the underlined should be (2, 1) and you should end with your last offset value of 6 and not 7, i placed my copysheet in the middle and it changes the code...

    Can someone please advise further on how we can troubleshoot this, what a useful script, you rock Chandoo...

     Dim strListSheet As String, strcopysheet As String
       
       
        strListSheet = "List"
       
        On Error GoTo ErrH
        Sheets(strListSheet).Select
        Range("B2").Select
       
        'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
        Set currentWB = ActiveWorkbook
        Do While ActiveCell.Value <> ""
           
            strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
            strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
            strcopysheet = ActiveCell.Offset(0, 4).Value
            strWhereToCopy = ActiveCell.Offset(0, 5).Value
            strStartCellColName = Mid(ActiveCell.Offset(0, 6), 2, 1)
           
            Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
            Set dataWB = ActiveWorkbook
           
            Sheets(strcopysheet).Range(strCopyRange).Select

  15. Chris says:

    Sabir-

    Would you mind pasting your full code?  I changed some things to try to match what was working for you, but I am still getting the error message when I use "Sheets(strCopySheet).Range(strCopyRange).Select" instead of "Range(strCopyRange).Select"

    I think maybe the problem is something to do with the sheets not being activated...?

    So close, yet so far away!

  16. Nitesh Kotian says:

    Dear Sir,

    I have a master sheet with 23 columns which needs to be updated on daily basis and, for this 23 cloumns i receive data from different departments, in 5 diffeent sheets,

    Further, 5 different sheets data are huge, so i want to know how to update my master sheet from the 5 different sheet.

    I want such formula that without touching my mater file it should get updated automatecially, from the data i received from different sheets.  

  17. Andrew says:

    I have to consolidate a number of separate files which will have varying number of rows, also each new data needs to be appended onto the last, so that at the end of each month I have collected all the data for that month.  I intend using this to create chart.  How can I change the vb code in this article to do this?

  18. Cho7tom says:

    Thanks for this great article.
    I tried to adapt the code to my own needs and I encounter an error with the 'workbooks.open()' function.
    For test purposes, I wrote this sub :
    Sub TEST()
        Dim testWB As Workbook
        Dim strFileName As String
        strFileName = ActiveWorkbook.Path & "\test.xlsm"
        Set testWB = Workbooks.Open(strFileName)
        MsgBox "opened!"
        testWB.Close
        MsgBox "closed!"
    End Sub
    This sub procedure works fine the first time I launch it after having opened my Excel file.
    The issue appear when I launch it several time (like if I wanted to open / close the file several times), I obtain the following error :
    "runtime error 1004. Method open of object workbook failed".
    Do you have any explaination / way to improve this TEST sub?
     
    Thank you in advance for any help!
    Cho7tom
     

  19. Kiran says:

    Iam getting Error as Userdefined Type not Defined,even though i have defined,kindly help me rectifying this
     

  20. Craig Harman says:

    Hi Guy's

    A quick one hopefully.
    I love this and have got it to work in a way but i really need help with this.....
    I have Supplier's name in col A and then from Col B-M the headings are April to March.
    I have separate files for each month, I want to copy the data for each month when I press update to the relevant column.
    Each File is exactly the same but obviously all difference spend figures.

    Hope this makes sense and someone can help!

    Thank you

  21. Amr says:

    Hi Guys
    this is awesome, this is exactly what i want but i need to add another field List sheet for the sheet name, can anyone help

  22. Smallman says:

    Hi All

    The above code in the Blog Post can be made more efficient. Here is a link to a thread on the Chandoo forum with a file to show workings. Below is the code.

    Sub ConsolidateDta()
    Dim i As Integer
    Dim fil As String
    Dim Col As String
    Dim cpy As String
    Dim ws As Worksheet
    Dim twb As Workbook

    Set ws = Sheet1 ' List sheet

    Application.DisplayAlerts = False
    Set twb = ThisWorkbook
    On Error GoTo Err 'This is just in case a muppet mistypes a path or file name.

    For i = 2 To ws.Range("B65536").End(xlUp).Row 'Sheet1 is MasterSheet
    fil = ws.Range("C" & i) & ws.Range("B" & i) 'File Location plus XL name
    cpy = ws.Range("D" & i) & ":" & ws.Range("E" & i) 'Copy Range
    Col = Left(ws.Range("B" & i), 1) 'Col to paste to
    Workbooks.Open fil, 0, 1 'Open Read Only
    Range(cpy).Copy
    twb.Sheets(ws.Range("F" & i).Value).Cells(Rows.Count, Col).End(xlUp)(2).PasteSpecial 12 'Vals only
    ActiveWorkbook.Close False 'Close no save
    Next i
    Application.DisplayAlerts = False
    Exit Sub
    Err: 'Mup Mup
    MsgBox "The file " & ws.Range("b" & i) & " is missing. Operation incomplete."
    End Sub

    Take care

    Smallman

  23. Pooja says:

    Hi,

    I am using Chandoo's code (as below) and it is working great. But i have additional requirement on this code. I am using this code for a Dahsboard where I need to refresh and run the macro multiple times. Here each time i run the macro the new data is pasted one below another, creating duplications.

    Is it possible to erase the previous data and paste the new one each time I run the macro. Please help as I am stuck here.

    Public strFileName As String
    Public currentWB As Workbook
    Public dataWB As Workbook
    Public strCopyRange As String

    Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strLinkSheet As String
    Dim sheetname As String

    strLinkSheet = "Link"

    On Error GoTo ErrH
    Sheets(strLinkSheet).Select
    Range("B2").Select

    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value ""

    strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
    strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
    strWhereToCopy = ActiveCell.Offset(0, 4).Value
    strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

    Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
    Set dataWB = ActiveWorkbook

    Range(strCopyRange).Select
    Selection.Copy

    currentWB.Activate
    Sheets(strWhereToCopy).Select
    lastRow = LastRowInOneColumn(strStartCellColName)
    Cells(lastRow + 1, 1).Select

    Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
    Application.CutCopyMode = False
    dataWB.Close False
    Sheets(strLinkSheet).Select
    ActiveCell.Offset(1, 0).Select
    Loop
    'activates sheet of specific name

    Worksheets("Dashboard Project view").Activate
    Exit Sub

    ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
    End Sub

  24. Patrick says:

    Hi,

    This macro is great for grabbing data from different workbook.
    If I would like to grab data from different workbook and also different worksheet is it possible?

    Please help and advice.

    Thanks in advance.

  25. Aman says:

    Hi,

    I am having trouble with this macro. The error dialog box keeps popping up. Please help me find the error. Thanks.

    Public strFileName As String
    Public currentWB As Workbook
    Public dataWB As Workbook
    Public strCopyRange As String

    Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String
    Dim strCopySheet As String

    strListSheet = “List”

    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("H2").Select

    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value “”

    strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
    strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
    strWhereToCopy = ActiveCell.Offset(0, 4).Value
    strCopySheet = ActiveCell.Offset(0, 6).Value
    strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

    Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
    Set dataWB = ActiveWorkbook

    Sheets(strCopySheet).Select
    Range(strCopyRange).Select
    Selection.Copy

    currentWB.Activate
    Sheets(strWhereToCopy).Select
    lastRow = LastRowInOneColumn(strStartCellColName)
    Cells(lastRow + 1, 1).Select

    Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
    Application.CutCopyMode = False
    dataWB.Close False
    Sheets(strListSheet).Select
    ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

    ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
    End Sub

    Public Function LastRowInOneColumn(col)
    ‘Find the last used row in a Column: column A in this example
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
    End Function

  26. Rajesh says:

    Hi...
    Need to extract the specific cells from different files and consolidate in single sheet. 3 Cells like A2, D2, E2 and put in consolidated file. Only one Row of data need to be fetched from each file.

    Can you help to share a code for that?
    Thanks in advance.
    Regards,
    Rajesh

  27. Misterman says:

    Hi Chandoo,

    Thank you for posting this.

    Having trouble trying to add another function in.
    I am trying to have the Data Range End Cell to not be set.
    So all the files will only have Data Range Start Cell.

    I have tried to add new range but it seems like I'm not understanding the scope of the ActiveWorksheets.

    Can anyone help with this?

    Thanks

  28. Wen says:

    Dear Sir
    I try to use your code but find
    there is error
    1 Red words for Dim strListSheet As StringstrListSheet = “List”
    2 and Range(“B2?).Select

    3 and then it said it said no this sub or function
    LastRowInOneColumn(strStartCellColName)

    the code I used from above-

    Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As StringstrListSheet = “List”

    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range(“B2?).Select

    ‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value “”

    strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
    strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
    strWhereToCopy = ActiveCell.Offset(0, 4).Value
    strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

    Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
    Set dataWB = ActiveWorkbook

    Range(strCopyRange).Select
    Selection.Copy

    currentWB.Activate
    Sheets(strWhereToCopy).Select
    lastRow = LastRowInOneColumn(strStartCellColName)
    Cells(lastRow + 1, 1).Select

    Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
    Application.CutCopyMode = False
    dataWB.Close False
    Sheets(strListSheet).Select
    ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

    ErrH:
    MsgBox “It seems some file was missing. The data copy operation is not complete.”
    Exit Sub
    End Sub

  29. Smallman says:

    Hi Wen

    1 - 1 Red words for Dim strListSheet As StringstrListSheet = “List”

    Should be;

    Dim strListSheet As Stringstr
    ListSheet = “List”

    2. Range(“B2?).Select

    In the context of the blog post this should be;

    Range(“B2").Select

    3. and then it said it said no this sub or function
    LastRowInOneColumn(strStartCellColName)

    This most likely does not work because it stems from part 2 where a cell needs to be selected in the first place.

    strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

    See the word Activecell above? If cell B2 never gets selected then I assume this part will fail.

    Take care

    Smallman

  30. Smallman says:

    Sorry I made a mistake. The first line of 1 should be this. It is a string.

    Dim strListSheet As String

    Smallman

  31. M B Sridharan says:

    I wanted to consolidated text comments for financial variance. If i update comments for the months it should consolidated the comments in Ytd(For eg., i am working for the month May my variance 9k this is relates to volume increase till april i have around 15k it should add the 9k and display as 24k Volume variance. is it possible in Excel.

  32. kiran says:

    Hi,

    How can I copy a value from one excel sheet to another sheet of a different workbook.
    Could you please illustrate with an example.

    Thanks!
    Kiran..

  33. Mukesh says:

    i have used your macro and it did as i wanted, however i have small twist.. i have added a additional column for Yes or no... i want to macro to run only when it has "yes" in the row for that particular file and do nothing if has "no". there are multiple file from which i extract my data but few files i dont want them to copy paste to master file.. Please help.

    Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String
    Dim strCopySheet As String
    Dim StrRunmacro As String
    Dim Cell As Range

    strListSheet = "Macro List"

    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("B2").Select

    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    For Each Cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    If Cell.Value Like "?*.?*" And _
    LCase(Cells(Cell.Row, "i").Value) = "yes" Then

    strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
    strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
    strWhereToCopy = ActiveCell.Offset(0, 4).Value
    strCopySheet = ActiveCell.Offset(0, 6).Value
    strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
    StrRunmacro = ActiveCell.Offset(0, 7).Value

    Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
    Set dataWB = ActiveWorkbook

    Sheets(strCopySheet).Select
    Range(strCopyRange).Select
    Selection.Copy

    currentWB.Activate
    Sheets(strWhereToCopy).Select

    Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
    Application.CutCopyMode = False
    dataWB.Close False
    Sheets(strListSheet).Select
    ActiveCell.Offset(1, 0).Select

    End If
    Next Cell

    Exit Sub

    ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
    End Sub

  34. geetu says:

    I am align to macros. Isit possible to include the tab name and copy rest of the information and what needs to be added if I am not sure about the data end cell range.

  35. L3g4to says:

    First to say - I really appreciate your site! Now on the subject: VBA is in my opinion too often the first tool most Excel users reach out to in such cases. Which is WRONG IMHO. Consolidating/joining/deduplicating workbooks/worksheets/data are typical applications for SQL. Consolidating 2 worksheets into 1 can be done as easy as in one line of SQL! See below

    SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM [Sheet2$]

    And what is more you can refresh the Query with 2 clicks of the mouse and don't need to save the file as a less-secure XLSM! What is more (OLEDB/ADODB) is something NATIVELY available and supported in Excel!

    I welcome you to see my AddIn which I hope which aid some in the journey of learning to use SQL in Excel.
    http://www.analystcave.com/excel-tools/excel-sql-add-in-free/

    I intend to also elaborate more on this in my VBA tutorial: http://www.analystcave.com/tutorials/excel-vba-tutorial/#Excel_VBA_Tutorial_What_next

  36. Sandhya says:

    Hi Friends,

    I am using the below code i.e. consolidate the different excel files into one excel sheet, but consolidate location would start from Column G or H it will vary, kindly suggest me how to modify this code;

    Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As StringstrListSheet = “List”
    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range(“B2”).Select

    ‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value “”

    strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
    strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
    strWhereToCopy = ActiveCell.Offset(0, 4).Value
    strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

    Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
    Set dataWB = ActiveWorkbook

    Range(strCopyRange).Select
    Selection.Copy

    currentWB.Activate
    Sheets(strWhereToCopy).Select
    lastRow = LastRowInOneColumn(strStartCellColName)
    Cells(lastRow + 1, 1).Select

    Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
    Application.CutCopyMode = False
    dataWB.Close False
    Sheets(strListSheet).Select
    ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

    ErrH:
    MsgBox “It seems some file was missing. The data copy operation is not complete.”
    Exit Sub
    End Sub

  37. Ajay Kumar says:

    Hi all,

    Can anyone please help me below i have added three more column H ,I and J In H Column i would like to give manual sheet name which i want to copy particular sheet in H column i would like to print the sheet name from where data copied and in J column i would like to print how many rows are copied from that particular sheet .
    Can anyone help me please i need this one

    Best Regards
    Ajay

  38. AMir says:

    Hi,

    If in the above example I want to add 10 more ranges from each opened file and paste it into "Masterdata", how to do it pls?

  39. Naveen says:

    hi...
    I wanted to copy data from some source file(excel sheets) and paste them (over writing the previous contents except the column header) in master excel. and delete those source files.

    can any one help on this please...

  40. Naveen says:

    hi please ignore the above comment..

    the actual request is this..
    Hi…
    1. I wanted to copy whole data from 2 or 3 excel files which will contain a single sheet inside and paste them (over writing the previous contents except the column header) in master excel. and delete those source files.
    2. and this should repeat once in every day at a given time, automatically using macro.
    can any one help me on this please..

  41. Lizbeef says:

    Thank you so much for this consolidate data vba code! I have been trying to work out how to do exactly this - and your explanation is so clear! Brilliant Chandoo!

  42. Lizbeef says:

    Sorry I also had a question to ask regarding this but was so excited at the solution I forgot to add it!

    What I would like to do is either cut the data from my closed workbooks or clear the contents of the range after it has been inserted in the active workbook.

    I have changed the code so that files opened are not read only and then save files when they close. And then I tried amending to cut instead of copy - I get the file missing message. So i tried adding Selection.ClearContents after Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone and again get the missing file message.

    Is it possible to clear the contents after they have been copied please?

  43. Lizbeef says:

    Hello Again!

    I think I have solved my own query through trial and a LOT of error!

    I realised last night that I cannot cut and paste special values so I concentrated on clearing the cells. I realised that what I really wanted to do was delete the rows I had just copied so I have changed your marvellous code a little to open open source files, copy data to clipboard then delete the rows, paste values into master sheet then close the source file and saving changes.

    I am so grateful to you as I have really been struggling with this! I have copied your amended code below in case anyone else has this same question - thank you very much indeed!

    Public strFileName As String
    Public currentWB As Workbook
    Public dataWB As Workbook
    Public strCopyRange As String

    Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String

    strListSheet = "List"

    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("B2").Select

    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value ""

    strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
    strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
    strWhereToCopy = ActiveCell.Offset(0, 4).Value
    strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

    Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=False
    Set dataWB = ActiveWorkbook

    Range(strCopyRange).Select
    Selection.Copy
    Selection.EntireRow.Delete

    currentWB.Activate
    Sheets(strWhereToCopy).Select
    lastRow = LastRowInOneColumn(strStartCellColName)
    Cells(lastRow + 1, 1).Select

    Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
    Application.CutCopyMode = False
    dataWB.Close Tr

    Sheets(strListSheet).Select
    ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

    ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
    End Sub

    Public Function LastRowInOneColumn(col)
    'Find the last used row in a Column: column A in this example
    'http://www.rondebruin.nl/last.htm
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
    End Function

  44. Lizbeef says:

    Oh dear, I am so disappointed. My amendment of your code works perfectly on my PC at home - actually a Mac - but when I use exactly same code at work I get missing file message again. It seems that data copied to clipboard but then won't paste so error occurs? It works when I take out selection.entrierow.delete. - please can you help?
    If no one seeing this can I ask this question somewhere else please?

  45. LeonSA says:

    Hi all,

    Thank you for the VBA code - works perfectly fine:).
    How would the code me modified if to be used with closed workbooks?

    Kind regards,

    Leon

  46. Saniv says:

    Hello Guys,

    Can you please help me in a macro to combine 10 worksheets in one file.
    All Worksheets named with "Country"
    and below are the columns I require data from different worksheets.

    Customer Entity
    Invoice Number
    Invoice Date
    Invoice Currency
    Total Invoice Amount
    Amount Outstanding at Report Date
    Days Elapsed Since Invoice Issued
    Specific Reason for Non-Payment of Invoice
    Owner of Dispute
    Dispute Code(per agreed list in Reason Code schedule)
    Any Brief Additional Information
    Action to be Taken and by Whom
    Date Action to be Taken By
    GBP amount

  47. Oliver says:

    Dear
    Is it possible to run the macro without the need to open the files.
    I tested with my links and files on a server and it took very long time to open the files.
    Maybe with a direct connection ?
    Thanks
    Olivier

  48. zelche28 says:

    Hi,

    I am having trouble with this macro. The error dialog box keeps popping up. Please help me find the error.

    And aside from the range I need consolidate I also need to consolidate some single cells in the same worksheet.

    Can anyone help me please?

    Thanks in advance!

  49. Sourav says:

    Hello,

    Scenario: I have a team. For every individuals, there is one excel with three sheets. Each individual have to enter data on all the sheets based on their work. All the excel files are shared with them via cloud so that I too have access on those data.

    Now I need help in doing the following:
    1. Downloading the individual sheets ans then saving them on a user defined folder. I assume that as the excel files are on the cloud, their address will not change. So I an make a macro to fetch the individual files from the appropriate locations and save them locally whenever the specific macro will run.

    2. Next, I would need to collate the data to a master file in such a way that all the data of all the sheet 1 of all the copied excel will be in me sheet of master file. for example, excel 1 and excel 2 are two excel files with sheet 1, sheet 2 and sheet 3. When I run a macro, all the data of sheet 1 (for both excel 1 and excel 2 will be consolidated to a master file's sheet 1. Similarly the data of Sheet 2 and 3 will be consolidated to Master file's sheet 2 and 3 respectively)

    Please help me in this.

    thanks i advance ! 🙂

  50. Manjunatha says:

    I have around 30+ workbooks, For consolidation purpose i have to Ignore Blank data files. How can i go ahead?

  51. Kayla says:

    Can this code be modified to paste the data in the next available column, rather than the next available cell in a single row?

  52. Kayla says:

    Is there a way to have the data paste in the next available column instead of row? I have data where I want the final result to contain a table where each column contains data from each individual file.

  53. HI
    I WANT TO USE DIFFERENT WORKBOOKS RANGES IN VLOOKUP FUNCTION AS NEAR BY COLOUMN VLOOKUP VALUE AS FILE FILE NAME

  54. Mays says:

    Hello
    My friend has a new work from home job.
    She is requested to copy only one column (Column I) from 3060 workbook into a single workbook.

    The ninth column to be specific.
    How can this be done?! i appreciate your support and help.

Leave a Reply