fbpx

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.

    You want to learn

    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

    Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
    Rebekah S
    Reporting Analyst
    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.

    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