Qazzim Dungey
asked on
consolidate multiple excel files to one
I have an issue. I have a whole bunch of files in a folder with various different names. The files I want to merge have the word REC at the start of the file
e.g REC_012014.xlsx, REC_022014.xslx..etc. They all have the same format, and I just want to extract the first sheet with range A1:D50 in all the files and merge them into one excel file. How do I go about doing this?
e.g REC_012014.xlsx, REC_022014.xslx..etc. They all have the same format, and I just want to extract the first sheet with range A1:D50 in all the files and merge them into one excel file. How do I go about doing this?
You would easily accomplish this with free Add-In of Ron de Bruin http://www.rondebruin.nl/win/addins/rdbmerge.htm
ASKER
JulianH, your are almost there. What I want to do with it is merge it vertically, so it joins all the files and is the exact same as the other reports, the only difference is this one will just have a larger amount of data. The issue I am having is I am trying that but how do I keep record of the last row, so I can append without overwriting. I am fairly new to VBA.
That's easy you are copying out 50 rows at a time so you have a loop variable that starts at 0.
Each time you go round you paste to the row = loop_variable * 50
Something like this
Each time you go round you paste to the row = loop_variable * 50
Something like this
Sub MergeFiles()
Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Dim sheet As Worksheet
Dim Row As Integer
Dim Cell as String
Application.ScreenUpdating = False
directory = "C:\test\"
fileName = Dir(directory & "REC*.xl??")
Row = 0
Do While fileName <> ""
Workbooks.Open (directory & fileName)
sheet = Workbooks(fileName).Worksheets(0)
sheet.Range("A1:D50").Select
Selection.Copy
Cell = "A" & ((Row * 50) + 1)
Range(Cell).Select
ActiveSheet.Paste
Row = Row + 1
fileName = Dir()
Loop
End Sub
ASKER
It is giving me a subscript out of range when I test it out. My guess it is referencing something out of the limit. Can you spot the error?
Qazzim,
Why not use the free Add-In of Ron de Bruin which exactly does what you need.
Why not use the free Add-In of Ron de Bruin which exactly does what you need.
What line is the error on?
ASKER
sheet = Workbooks(fileName).Worksh eets(0)
ProfessorjimJam, I am not using the utility because I need to get this macro for a bigger problem. This is just one small part to it.
ProfessorjimJam, I am not using the utility because I need to get this macro for a bigger problem. This is just one small part to it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
To use the above create a new Excel spreadsheet and create a macro with the above code - change the path name as required.You did not say how you wanted to merge the data so you will need to do that yourself but it is easy to work out the code simply do it manually once and record it in a macro then view the code.