Right now i have found a code and edited to suit my needs, the only issue i Have with this is i must select each workbook one by one by running this module below::
Private Sub Import_Data_Into_Current_Workbook_EE()
Dim NEWF As String
Dim SAVENAME As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
' active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.ButtonName = "&Open"
.InitialFileName = "C:\Users\osabbagh\Desktop\julyo"
.Filters.Clear
.Filters.Add "Excel files (*.xls; *.xlsm; *.xlsx)", "*.xls;*.xlsm;*.xlsx", 1
.Title = "Please Select an input file"
.AllowMultiSelect = False
End With
If Not (Application.FileDialog(msoFileDialogOpen).Show) Then
MsgBox "No File specified!.", vbExclamation, "Cancel has been pressed!"
Exit Sub
Else
customerFilename = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
SAVENAME = ActiveWorkbook.Name
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
On Error Resume Next
Set srcSht = targetWorkbook.Sheets(sourceSheet.Name)
On Error GoTo 0
If IsEmpty(srcSht) Then
sourceSheet.Copy Before:=targetWorkbook.Sheets(1)
customerWorkbook.Close
Else
MsgBox "Sheet name already there"
Exit Sub
End If
End If
NEWF = "C:\Users\osabbagh\Desktop\newfiles"
Application.Wait Now + TimeValue("00:00:01")
ActiveWorkbook.SaveAs NEWF & SAVENAME
End Sub
How would i go about letting this module run byitself for EACH workbook found in the folder "oldfiles"