Combining many Workbooks with the click of a Button

dbrull

Board Regular
Joined
Jun 25, 2002
Messages
71
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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.

Code:
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
 

Forum statistics

Threads
1,144,059
Messages
5,722,272
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top