主题：将若干部门预算表放置一个文件夹然后汇总 |
| 正文： |
预算取数各部门
Private Sub File1_Click()
File1.Path = "D:\青云富\9-预算执行情况\2010影城预算\2-2010年目标预算\2010年新街口店预算\新街口各部门预算数据"
File1.Pattern = "*.xls"
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Set xlapp = New Excel.Application
xlapp.Visible = True
Set xlbook = xlapp.Workbooks.Open("D:\青云富\9-预算执行情况\2010影城预算\2-2010年目标预算\2010年新街口店预算\23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909")
Dim i, x, ii As Integer
For i = 0 To File1.ListCount
Set xlb = xlapp.Workbooks.Open("D:\青云富\9-预算执行情况\2010影城预算\2-2010年目标预算\2010年新街口店预算\新街口各部门预算数据\" & File1.List(i))
If InStr(File1.List(i), "营销部") Then
ii = 2
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
Windows(File1.List(i)).Activate
ii = 4
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
Windows(File1.List(i)).Activate
ii = 5
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
End If
If InStr(File1.List(i), "营运部") Then
ii = 3
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
Windows(File1.List(i)).Activate
ii = 6
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
End If
If InStr(File1.List(i), "放映部") Then
ii = 7
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
End If
If InStr(File1.List(i), "财务部") Then
ii = 8
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Select
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
Range("a1").Select
ActiveSheet.Paste
Windows(File1.List(i)).Activate
ii = 9
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
Windows(File1.List(i)).Activate
ii = 10
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
End If
If InStr(File1.List(i), "行政部") Then
ii = 11
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
Windows(File1.List(i)).Activate
ii = 12
xlb.Worksheets(ii).Select
xlb.Worksheets(ii).Unprotect Password:="wandacaiwu"
Cells.Select
Selection.Copy
Windows("23-万达影城预算编制（底稿）－2010财年-南京万达影城-200909.xls").Activate
xlbook.Worksheets(ii).Unprotect Password:="wandacaiwu"
xlbook.Worksheets(ii).Select
Range("a1").Select
ActiveSheet.Paste
End If
xlb.Close (True)
100: Next i
xlbook.Close (True)
End Sub |
|
|