- 帖子
- 438
- 主題
- 67
- 精華
- 0
- 積分
- 531
- 點名
- 30
- 作業系統
- win7
- 軟體版本
- office 2010
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-10-30
- 最後登錄
- 2024-10-24
|
52#
發表於 2012-12-8 21:37
| 只看該作者
回復 50# GBKEE
Option Explicit
Sub Ex()
Dim Rng(1 To 2) As Range
With Workbooks("payment.XLSM").Sheets("2012")
Sheets("2012").Range("A2:L65536").ClearContents
Sheets("2012").Range("A2:L65536").Interior.Color = xlNone
.Range("A1").CurrentRegion.Offset(1) = ""
Set Rng(1) = .[e2]
With Workbooks.Open("C:\Documents and Settings\USER\桌面\Connie.XLSX").Sheets("SHEET1")
Set Rng(2) = .[A2:L2]
Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)
Rng(2).copy Rng(1).Cells(1, -3)
.Parent.Close False
End With
Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(2)
With Workbooks.Open("C:\Documents and Settings\USER\桌面\Lily.XLSX").Sheets("SHEET1")
Set Rng(2) = .[A2:L2]
Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)
Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)
Rng(2).copy Rng(1).Cells(1, -3)
.Parent.Close False
End With
Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(2)
With Workbooks.Open("C:\Documents and Settings\USER\桌面\Jane.XLSX").Sheets("SHEET1")
Set Rng(2) = .[A2:L2]
Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)
Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)
Rng(2).copy Rng(1).Cells(1, -3)
.Parent.Close False
End With
Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(2)
With Workbooks.Open("C:\Documents and Settings\USER\桌面\Jenny.XLSX").Sheets("SHEET1")
Set Rng(2) = .[A2:L2]
Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)
Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)
Rng(2).copy Rng(1).Cells(1, -3)
.Parent.Close False
End With
Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(2)
With Workbooks.Open("C:\Documents and Settings\USER\桌面\Patrick.XLSX").Sheets("SHEET1")
Set Rng(2) = .[A2:L2]
Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)
Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)
Rng(2).copy Rng(1).Cells(1, -3)
.Parent.Close False
End With
End With
End Sub
經過改良後，成功了，就成效寫出來與大家分享~~ |
|
發表於 2012-12-9 08:07
| 
