Merging excel files

  • Thread starter Thread starter Raul V
  • Start date Start date
R

Raul V

Hello everyone, I have one excel file for every day of the
month since June 2003. I would like to create one file per
month and merge the daily file into it...is there another
way to do it other than copy/paste? Please help there is
a lot of files
 
OK. forgot to ask about worksheets.
Are we to assume that each workbook is using ONLY sheet1 for the data to
merge?
if so, copy all of the below into a REGULAR module, (modified from Ron
deBruin's site)

http://www.rondebruin.nl/copy3.htm#sheet
'---------
Function Split97(sStr As Variant, sdelim As String) As Variant
' Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") &
"""}")
End Function

Sub TestFile4_modified() 'Ron de Bruin modified DBG
monthtoget = InputBox("Enter TWO digit month: ie 07")

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\A"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count

vArr = Split97(.FoundFiles(i), "\")
sFname = vArr(UBound(vArr))
If Left(sFname, 2) = monthtoget Then

Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
mybook.Close

End If

Next i
End If
End With
Application.ScreenUpdating = True
End Sub




'----------
 
Back
Top