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.
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.
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.
- 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.
71 Responses to “Consolidate data from different excel files (VBA)”
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
Fantastic Alex, thanks for the tip!
Hi,
Would it be possible to make it a little bit more flexible and allow the use of wildcard characters in a file name?
@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
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
how about copy from different workbooks?
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.
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!
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
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
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
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?
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.
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
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
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
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
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!
[...] Extract data from multiple files & place in one sheet [...]
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
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
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
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!
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.
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?
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
Iam getting Error as Userdefined Type not Defined,even though i have defined,kindly help me rectifying this
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
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
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
Hi Smallman-:))
would you please send me an excel file on file e-mail with this macro?
Thank you
My e-mal: kazdima@yahoo.ca
Thank you.
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
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
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.
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
@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
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
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
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
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
Sorry I made a mistake. The first line of 1 should be this. It is a string.
Dim strListSheet As String
Smallman
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.
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..
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
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.
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
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
@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.
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
@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.
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
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?
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...
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..
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!
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?
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
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?
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
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
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
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!
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 ! 🙂
Hello Sourav...
Thanks for your comments. Have you heard of Power Query? These kind of data collection, aggregation and clean-up are easy to do with PQ. I will be writing a tutorial on how to do this for your exact problem today. I will post another comment once the post is ready. You should really use Power Query for such problems. Meanwhile watch the Power Query getting started tutorial here - Powerful Introduction to Power Query.
Here you go - I just published an article explaining how to do this - https://chandoo.org/wp/combine-excel-files-using-power-query/
Good luck.
I have around 30+ workbooks, For consolidation purpose i have to Ignore Blank data files. How can i go ahead?
Can this code be modified to paste the data in the next available column, rather than the next available cell in a single row?
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.
HI
I WANT TO USE DIFFERENT WORKBOOKS RANGES IN VLOOKUP FUNCTION AS NEAR BY COLOUMN VLOOKUP VALUE AS FILE FILE NAME
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.