Try this:
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)
If Application.Ready = True Then
wb.Sheets("Index").Activate
ActiveSheet.UsedRange.Copy
AcWb.Sheets("Index").Activate
Range("A1000000").End(xlUp).Select
ActiveSheet.Paste
Application.CutCopyMode = False
wb.Close
End If
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/search?q=workbook
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NOORAIN ANSARI
Sent: Jan/Wed/2012 11:12
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Import data
Dear Selva,
Can you share a sample with Excel Macro Group.
On Wed, Jan 25, 2012 at 10:53 AM, LearnExcel <sendse...@gmail.com> wrote:
Hi team, i wanted to import data set from two files and consolidate
the data into a master file.
I need something in VBA, currently i run a marco that goes through
each sheet and copies them into the marco but for some reason it takes
some time im sure theres a better way..
let me know if you need more info
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
Thanks & regards,
Noorain Ansari
<http://excelmacroworld.blogspot.com/> http://excelmacroworld.blogspot.com/
<http://noorain-ansari.blogspot.com/> http://noorain-ansari.blogspot.com/
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
