SamT
08-23-2013, 12:11 PM
Option Explicit
Sub Macro1() '
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Comments
Dim MyFile As String
Dim MyCell As CellFormat
'Comments
Dim BottomOfC As Range
MyPath = InputBox("What folder are the files in?") & "\"
MyFile = Dir(MyPath & "*.xls?")
Do While MyFile <> ""
Workbooks.Open MyPath & MyFile
Set BottomOfC = Workbooks("Concession Sales Data.xlsm").Range("C1") _
.End(xlDown).Offset(1, 0)
With Workbooks(MyFile).Sheets("Goods Out of Stock Summary")
.Range("C14").Copy BottomOfC.Offset(0, -2)
.Range("C12").Copy BottomOfC.Offset(0, -1)
.Range("A20:L20").End(xlDown).Copy BottomOfC
.Close True
End With
MyFile = Dir
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Macro is done with this folder.", vbOKOnly, "Macro done."
End Sub
Sub Macro1() '
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Comments
Dim MyFile As String
Dim MyCell As CellFormat
'Comments
Dim BottomOfC As Range
MyPath = InputBox("What folder are the files in?") & "\"
MyFile = Dir(MyPath & "*.xls?")
Do While MyFile <> ""
Workbooks.Open MyPath & MyFile
Set BottomOfC = Workbooks("Concession Sales Data.xlsm").Range("C1") _
.End(xlDown).Offset(1, 0)
With Workbooks(MyFile).Sheets("Goods Out of Stock Summary")
.Range("C14").Copy BottomOfC.Offset(0, -2)
.Range("C12").Copy BottomOfC.Offset(0, -1)
.Range("A20:L20").End(xlDown).Copy BottomOfC
.Close True
End With
MyFile = Dir
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Macro is done with this folder.", vbOKOnly, "Macro done."
End Sub