Combining data from multiple workbooks

Prometheus_au

New Member
Joined
Jan 26, 2005
Messages
12
I have a folder with 440 files.
I would like to stack the data from B6:F30 on every file into a single sheet.

I have the VB code to do this with the data on sheets within a workbook, but now need to have the data moved from seperate workbooks.

Can this be done?

Cheers.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
Here is a quick alteration from a similar question this morning.
Change the sFolderPath = "I:\" to suit your folder path.

Code:
Sub CollectAll()
On Error GoTo Exit_Line
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim wbkTempBook As Workbook
Dim shtPasteSheet As Worksheet, shtTemp As Worksheet
Dim lngMaxRow As Long, lngCopyRows As Long, lngPasteRow As Long, lngIgnoreRows As Long

lngPasteRow = 7 'Row to start copying to
lngIgnoreRows = 5 'Number of Rows to ignore

Set shtPasteSheet = ThisWorkbook.Sheets(1)

sFolderPath = "I:\"

sTempName = Dir(sFolderPath & "*xls")
Do While sTempName <> ""
    Set wbkTempBook = Workbooks.Open(sFolderPath & "\" & sTempName, True, True)
    Set shtTemp = wbkTempBook.Sheets(1)
    lngMaxRow = 30
    lngCopyRows = lngMaxRow - lngIgnoreRows
    If lngMaxRow > lngIgnoreRows Then
        shtTemp.Range("A" & lngIgnoreRows + 1 & ":V" & lngMaxRow).Copy _
            shtPasteSheet.Range("A" & lngPasteRow & ":V" & lngPasteRow + lngCopyRows - 1)
        lngPasteRow = lngPasteRow + lngCopyRows
    End If
    wbkTempBook.Close (False)
    sTempName = Dir
Loop

Exit_Line:
Application.EnableEvents = True
Application.ScreenUpdating = True
If Err.Number <> 0 Then MsgBox Err.Description
End Sub
 

Prometheus_au

New Member
Joined
Jan 26, 2005
Messages
12
Thanks CraigM,

After some initial difficulty (with the user not the code) It's working fine and saving heaps of time.

Cheers
 

Forum statistics

Threads
1,147,846
Messages
5,743,521
Members
423,801
Latest member
paulj4177

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