Consolidate data from different excel files (VBA)

Posted on April 9th, 2012 in Automation , Excel Howtos , VBA Macros - 37 comments

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.

Your email address is safe with us. Our policies

Written by Vijay Sharma
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

37 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?

  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

Leave a Reply