Consolidate data from different excel files (VBA) Published on April 9th, 2012 Written by: Vijay Sharma Next Skip to Responses Prev 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 canuse 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. 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. 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.Openmethod. 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. More Examples on Consolidation What is VBA & Macros? Introduction Excel VBA Example Macros VBA tutorial videos 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. Share this tip with your friends Facebook9LinkedInTwitter10GoogleEmailPrint Categories: Automation, Excel Howtos, VBA Macros Share Article Save to Instapaper Next Back to Top Prev 51 Comments Alex Apr 09 - 2:01 pm Reply 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 Alexander Van Parys May 03 - 11:40 pm Reply Fantastic Alex, thanks for the tip! J Apr 10 - 10:20 am Reply Hi, Would it be possible to make it a little bit more flexible and allow the use of wildcard characters in a file name? Jason H Apr 10 - 4:48 pm Reply @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 Apr 24 - 1:22 pm Reply 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 Elmer Apr 12 - 10:13 am Reply how about copy from different workbooks? Jake Apr 17 - 5:59 pm Reply 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. Mike Ebert Apr 17 - 6:51 pm Reply 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! Sophia May 25 - 8:47 pm Reply 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 May 26 - 12:05 pm Reply 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 May 17 - 10:37 am Reply 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 Aug 28 - 7:51 am Reply 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 Oct 10 - 6:32 am Reply 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. Bene Jul 03 - 4:55 am Reply 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 Aug 28 - 5:58 pm Reply 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 Ron Jul 12 - 8:36 pm Reply 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 Addy Jul 24 - 6:36 pm Reply 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 vashisth Aug 21 - 10:04 am Reply 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! Even faster ways to Extract file name from path [quick tip] | Chandoo.org - Learn Microsoft Excel Online Oct 24 - 8:02 am […] Extract data from multiple files & place in one sheet […] Sabir Nov 09 - 7:48 am Reply 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 Chris Nov 13 - 5:24 pm Reply 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 Sabir Nov 14 - 5:52 am Reply 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 Chris Nov 14 - 2:24 pm Reply 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! Nitesh Kotian Nov 21 - 12:25 pm Reply 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. Andrew Dec 29 - 12:16 pm Reply 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? Cho7tom Feb 08 - 2:11 pm Reply 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 Kiran Apr 10 - 9:09 am Reply Iam getting Error as Userdefined Type not Defined,even though i have defined,kindly help me rectifying this Craig Harman Apr 24 - 2:55 pm Reply 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 Amr Oct 17 - 12:50 am Reply 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 Smallman Nov 02 - 11:49 pm Reply 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 Kazdima Nov 05 - 10:10 pm Reply Hi Smallman-:)) would you please send me an excel file on file e-mail with this macro? Thank you Kazdima Nov 05 - 10:13 pm Reply My e-mal: kazdima@yahoo.ca Thank you. Smallman Nov 05 - 11:14 pm Reply Hi The file is on the Chandoo Forum. Post 4 you can find the file. http://forum.chandoo.org/threads/question-about-chandoo-example-macro-consolidate-data-from-different-excel-files-vba.13033/#post-76891 Take care Smallman Pooja Dec 30 - 5:34 am Reply 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 Patrick Aug 27 - 8:12 am Reply 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. Aman Sep 11 - 3:37 pm Reply 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 Hui... Sep 12 - 2:32 am Reply @Aman The Code looks ok, but it may be that your data isn’t in the correct format ? Can you post the file or email it too me Rajesh Sep 16 - 12:38 pm Reply 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 Misterman Nov 24 - 10:13 am Reply 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 Wen Dec 10 - 1:52 pm Reply 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 Smallman Dec 11 - 3:50 am Reply 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 Smallman Dec 11 - 4:03 am Reply Sorry I made a mistake. The first line of 1 should be this. It is a string. Dim strListSheet As String Smallman M B Sridharan Jan 21 - 12:57 pm Reply 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. kiran Feb 05 - 11:23 am Reply 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.. Mukesh Feb 23 - 1:55 pm Reply 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 geetu May 14 - 10:18 pm Reply 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. L3g4to May 15 - 11:08 am Reply 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 Sandhya Oct 10 - 8:37 am Reply 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 Hui... Oct 11 - 9:05 am Reply @Sandhya Can you please post the question in the Chandoo.org Forums http://forum.chandoo.org/ Please attach a file so that a specific answer can be delivered. Sandhya Oct 11 - 11:12 am Reply Thanks you soooooooooooo much for your response. It is an urgent requirement. Kindly suggest me how to post the forum, i am unable to post my code or requirement. Kindly do the needful Hui... Oct 11 - 1:40 pm Reply @Sandhya You have to register to post questions there Goto http://forum.chandoo.org/ Goto Ask an Excel Question Post New thread Type your question Attach a file Please attach a file so that a specific answer can be delivered. Leave a Reply Name* E-Mail* Website Notify me of when new comments are posted via e-mail Notify me of follow-up comments by email. Notify me of new posts by email.
लेबल: excel
