Multiple files question

[alexm999]

i have several files with many rows of data. Each file is in the
format:
filename-1.xls
filename-2.xls
filename-3.xls

and so on... Is there an easy way to combine these files into 1 large
file named: filename-total.xls ?

I get about 3 to 12 of these files a day and i'm sick and tired of
combining them manually! Please help...

 

[Ron de Bruin]

Hi Alex

Look here for examples
http://www.rondebruin.nl/copy3.htm

 

[alexm999]

I looked in there. Just examples - now how can I get them to automate.
What if I have 3 files today, but only 2 tomorrow and 10 the next day?
I need a copy and paste code... Can anyone help?

 

[Johnny]

If the files are formatted in a fairly consistent way, I suggest either
writing an addin, or create a "master" workbook that goes out and grabs
the files (maybe in a specific folder, or by using a File dialog to
pick the files individually) and then pull them all into a new
workbook. The code here could get fairly involved, depending on what
the spreadsheets look like. If you want, you can send me a couple of
the workbooks and I can shoot you back an example. My email address
should be provided in the header of the post under view profile.

Thanks,
Johnny

 

[Mike Fogleman]

I have used this (from Ron's example) that allows me to select the exact
number of files to combine. It is not fully automated because you need to
tell it which files to combine using a browser dialogue box. It has to be
quicker than what you are doing now.
Sub DAC_Report()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
'MyPath = "C:\Data"
'ChDrive MyPath
'ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A3:F53")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

basebook.Worksheets(1).Cells(rnum, "G").Value = mybook.Name
' This will add the workbook name in column D if you want

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

' With sourceRange
' Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
' Resize(.Rows.Count, .Columns.Count)
' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
Columns("G:G").Font.Size = 8
Columns("G:G").Font.Bold = True
' ChDrive SaveDriveDir
' ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Mike F

 

[alexm999]

Hey, thanks for the code! Works great, but I need some modifications.
I need the Data to copy all the columns. Sometimes they end with colum
P sometimes they go to AJ. Is there a way to copy to the last column?

Also, How do I make it an Add-In for a simple button.

 

[Ron de Bruin]

Hi alex

You can find all the code on the page
http://www.rondebruin.nl/copy3.htm

For example this one
http://www.rondebruin.nl/copy3.htm#header

If you have the files in the same folder you can run this macro from a button

Play with the code and learn from it

 

[Guest]

Hi Johnny,
I have a similar problem, can I send you my sample files. Briefly my problem
is like ..
I want to combine sheets such as advance(sheet1),deposits(sheet2),
creditors(sheet3), so on...Sheet names are unique. In advance sheet
validation must be done at h column starting row 6 for value & grab the row
until value = "LLINE" or BLANK, similarly it should check value in g column
for deposit sheet, i column in prepaid sheet, & so on... The consol file
should have sheets advance, deposit, prepaid, & so on with data from all
files.
I am using excel 2002.

 

[Johnny]

Sure

 

[Guest]

Wow Johnny,
Where you on world tour. With Mr Ron's guidance, I finished it.
Anyway I thank you very much for responding.