Hi. I have a macro that merges all excel workbooks within a given folder
into one master workbook (and on 1 sheet). i need to do this every day
as new workbooks go into the folder. Because of this i need to delete
the workbooks that i have copied so that they are not copied again the
next day. Can anyone help please? I am sure its fairly easy and maybe
just a change by the "close workbook" line, but if you do not know then
you do not know i guess. Thanks in advance. Here is the code i'm using:
Sub ImportData()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim LastRow As Long
'\\ Disable Application Defaults to remove screen flicker, messages,
Etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
'\\ Set folder to work from
Path = "\\Holding folder\" 'Change path as needed
FileName = Dir(Path & "\*.xls", vbNormal)
If FileName = "" Then
MsgBox "There are no files awaiting import", vbOKOnly
Exit Sub
End If
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
'\\ Copy from workbooks and paste into this workbook.
If Range("A4") <> 0 Then
LastRow = Range("A65536").End(xlUp).Row + 1
Range("A4:G" & LastRow).Select
Selection.Copy
Windows("Merging Tool.xls").Activate
If Range("A4") <> 0 Then
LastRow = Range("A65536").End(xlUp).Row + 1
Range("A" & LastRow).Select
ActiveSheet.Paste
Else
Range("a4").Select
ActiveSheet.Paste
End If
End If
'\\ Close Workbooks
Wkb.Close SaveChanges:=True
FileName = Dir()
Loop
'\\ Re-enable application defaults
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
--
kardifflad