PDA

View Full Version : test



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