Combining many Workbooks with the click of a Button

[dbrull]

I have a number of people who will be creating workbooks each with only one tab but each (tab)with a unique name to that workbook on a monthly basis. They will all store their workbooks in the same folder on a shared drive.

I would like to have a master workbook that will go out to which ever work books are out there(number will vary each month as will names) and copy the data to a worksheet in the master. Placeing the data from one workbook directly under the data from the last one imported.

The data will be formatted the same in each of the workbooks (same number of columns with the same column headings, but the number of rows in each will vary.

Is this possible? How?

 

[dk]

On 2002-09-10 10:05, dbrull wrote:
I have a number of people who will be creating workbooks each with only one tab but each (tab)with a unique name to that workbook on a monthly basis. They will all store their workbooks in the same folder on a shared drive.

I would like to have a master workbook that will go out to which ever work books are out there(number will vary each month as will names) and copy the data to a worksheet in the master. Placeing the data from one workbook directly under the data from the last one imported.

The data will be formatted the same in each of the workbooks (same number of columns with the same column headings, but the number of rows in each will vary.

Is this possible? How?

Hello,

OK, this is entirely possible but involves you using some VBA code. I've written you a little bit of code which should be some use to you. Open the VB editor (Alt F11) and choose Insert, Module. Then paste this code. You'll need to modify it as I've said in the comments before it will work properly. Let me know how you get on.

Sub ImportIntoMaster()
Dim strSourceDataFolder As String
Dim lngFileCounter As Long
Dim shtDestination As Worksheet
Dim shtSource As Worksheet

'You will need to change these two variables to suit your needs

'strSourceDataFolder is the folder where your data files reside.  The Master workbook should NOT reside there
strSourceDataFolder = "C:data"

'Change this to the name of the worksheet where you want the data to end up
Set shtDestination = ThisWorkbook.Sheets("Master")


'This will prevent the message 'There is a large amount of information on the
'clipboard, do you want to be blah blah' from appearing

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Now search for all files in the source folder.  Open each of those files, copy the used range
'from each of those files and copy it into the worksheet defined in the variable shtDestination
With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = strSourceDataFolder
    .Execute
    For lngFileCounter = 1 To .FoundFiles.Count
        Set shtSource = Workbooks.Open(.FoundFiles(lngFileCounter)).ActiveSheet
        shtSource.UsedRange.Copy
        shtDestination.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
        ActiveWorkbook.Close False
    Next lngFileCounter
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

EDIT
I added the two screenupdating lines to stop the screen showing what's going on. This also has the advantage that it will speed up the code.
_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-09-10 10:48