|
把一个实例贴一下,以后参考用
Sub Costing()
Dim sfile As Date
Application.DisplayAlerts = False
sPath = "\\server01\Admin_Data\General Office\Angel\taz Costings\Weekly Costing\"
sfile = InputBox("Please Enter Week End Date", "Enter Date", Date) 'Friday
Masterfile = Format(sfile, "yyyymmdd")
sfile0 = sfile
sfile1 = DateAdd("d", -1, sfile) 'Thursday
sfile2 = DateAdd("d", -2, sfile) 'Wednesday
sfile3 = DateAdd("d", -3, sfile) 'Tuesday
sfile4 = DateAdd("d", -4, sfile) 'Monday
sfile0 = Format(sfile0, "ddmmyy")
sfile1 = Format(sfile1, "ddmmyy")
sfile2 = Format(sfile2, "ddmmyy")
sfile3 = Format(sfile3, "ddmmyy")
sfile4 = Format(sfile4, "ddmmyy")
Application.ScreenUpdating = False
If Worksheets("Livestock").CheckBox1.Value = True Then
Workbooks.Open Filename:=sPath & sfile0 & ".xlsx"
Windows(sfile0 & ".xlsx").Activate 'choose Friday file
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A1:A100").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Windows(sfile0 & ".xlsx").Activate
Windows(sfile0 & ".xlsx").Close False
End If
sfile = DateAdd("d", -1, sfile)
If Worksheets("Livestock").CheckBox2.Value = True Then
Workbooks.Open Filename:=sPath & sfile1 & ".xlsx"
Windows(sfile1 & ".xlsx").Activate 'choose Thursday file
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A101:A200").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Windows(sfile1 & ".xlsx").Activate
Windows(sfile1 & ".xlsx").Close
End If
sfile = DateAdd("d", -1, sfile)
If Worksheets("Livestock").CheckBox3.Value = True Then
Workbooks.Open Filename:=sPath & sfile2 & ".xlsx"
Windows(sfile2 & ".xlsx").Activate 'choose Wednesday file
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A201:A300").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Windows(sfile2 & ".xlsx").Activate
Windows(sfile2 & ".xlsx").Close
End If
sfile = DateAdd("d", -1, sfile)
If Worksheets("Livestock").CheckBox4.Value = True Then
Workbooks.Open Filename:=sPath & sfile3 & ".xlsx"
Windows(sfile3 & ".xlsx").Activate 'choose Tuesday file
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A301:A400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Windows(sfile3 & ".xlsx").Activate
Windows(sfile3 & ".xlsx").Close
End If
sfile = DateAdd("d", -1, sfile)
If Worksheets("Livestock").CheckBox5.Value = True Then
Workbooks.Open Filename:=sPath & sfile4 & ".xlsx"
Windows(sfile4 & ".xlsx").Activate 'choose Monday file
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A401:A500").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Windows(sfile4 & ".xlsx").Activate
Windows(sfile4 & ".xlsx").Close
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub |
|