Try this Code :
Sub Compile()
On Error GoTo Err_Clear:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Fso As New Scripting.FileSystemObject
Dim Path As String
Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to
Pick Downloaded Bills"
Application.FileDialog(msoFileDialogFolderPicker).Show
Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) &
"\"
If Path = "" Then Exit Sub
Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to
Save Compiled File"
CompilePath = Application.FileDialog(msoFileDialogFolderPicker).Show
compiledPath =
Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If compiledPath = "" Then Exit Sub
Dim Counter
Dim File As File
Dim FOlder As FOlder
Dim wb As Workbook
Dim ws As Worksheet
Dim AcWb As Workbook
Set AcWb = ActiveWorkbook
ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "Index"
Set FOlder = Fso.GetFolder(Path)
For Each File In FOlder.Files
Counter = Counter + 1
Set wb = Workbooks.Open(Path & File.Name)
wb.Sheets("Index").Activate
ActiveSheet.UsedRange.Copy
AcWb.Sheets("Index").Activate
Range("A1000000").End(xlUp).Select
ActiveSheet.Paste
Application.CutCopyMode = False
wb.Close
Next
If Counter > 0 Then
AcWb.SaveAs compiledPath & "Compiled", xlExcel12
AcWb.Close
End If
Err_Clear:
Err.Clear
Resume Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
If Counter < 1 Then
MsgBox "No File Found For Compile", vbInformation
Else
MsgBox Counter & " File Has been Compiled, Please Find your File at"
& vbCrLf & compiledPath, vbInformation
End If
End Sub
http://excelpoweruser.blogspot.com/2011/07/compiling-workbooks.html
Rajan.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Wednesday, August 17, 2011 6:48 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Workbooks Consolidation Macro
For a simple solution, simply record a macro while doing it manually.
From: Satish Bandaru <mailto:snband...@gmail.com>
Sent: Wednesday, August 17, 2011 1:06 AM
To: excel-macros@googlegroups.com
Subject: Re: FW: $$Excel-Macros$$ Workbooks Consolidation Macro
hi experts
please help me.plz find the atachment
thanks in advance
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
