- 帖子
- 124
- 主題
- 43
- 精華
- 0
- 積分
- 167
- 點名
- 0
- 作業系統
- Windows7
- 軟體版本
- office2007
- 閱讀權限
- 20
- 性別
- 男
- 來自
- 台灣台北
- 註冊時間
- 2011-3-16
- 最後登錄
- 2021-10-7
|
修改代碼
本帖最後由 vinejason 於 2014-6-6 12:25 編輯
平安
有4個Sheet 分別是銷貨帳單 與 銷貨明細 與 書目 與 客戶資料
下列代碼可輸入銷貨明細的編號 選擇列印
例如輸入 s10305101~s10305110
可抓取資料進行列印此十張不同各戶的銷貨單
只是執行速度相當緩慢
Public Sub prtbill(bgnum, ndnum)
Application.StatusBar = "報表列印執行中@@@@@敬請稍候!"
Sheet1.Cells.AutoFilter
Dim rng As Range
Sheet2.Activate '銷貨帳單
Sheets("銷貨帳單").Unprotect '避開保護
Set rng = Union(Range("B9:D32"), Range("F9:F32"), Range("H9:H32"), Range("C4:D7"), Range("C3"), Range("G5:G6"))
rng.ClearContents
fmidx = CLng(bgnum)
toidx = CLng(ndnum)
With Sheet3 '銷貨清單
For i = fmidx To toidx
rr = 2
nn = 9
Do Until .Cells(rr, "c") = Empty
If .Cells(rr, 3).Value = i Then
Cells(3, "g") = .Cells(rr, 3).Text
Cells(3, "c") = .Cells(rr, 1)
Cells(4, "g") = .Cells(rr, 4)
Cells(7, "g") = .Cells(rr, 9)
Cells(nn, "b") = .Cells(rr, "e")
Cells(nn, "d") = .Cells(rr, "f")
Cells(nn, "f") = .Cells(rr, "g")
Cells(nn, "h") = .Cells(rr, "l")
With Sheets("客戶資料")
Set F1 = .Columns(1).Find([C3])
[c4] = .Cells(F1.Row, 2)
[c5] = .Cells(F1.Row, 7)
[c6] = .Cells(F1.Row, 6)
[G5] = .Cells(F1.Row, 4)
[G6] = .Cells(F1.Row, 3)
End With
nn = nn + 1
If nn = 33 Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
rng.ClearContents
nn = 9
End If
End If
rr = rr + 1
Loop
If nn > 9 Then ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
rng.ClearContents
Next i
End With
ActiveSheet.Protect '恢復保護
Set BillRange = Worksheets("銷貨清單").Range("C1:C65535") '貨單編號遞增
n = Application.WorksheetFunction.Max(BillRange)
Sheets("銷貨帳單").Select
Range("G3").Select
ActiveCell.FormulaR1C1 = n + 1
Sheets("銷貨帳單").Select
Range("C3").Select
'Range("R9:R32").ClearContents
Set x = Cells.Find("xx", lookat:=xlPart) '還原"尋找框"不勾選狀態
Set rng = Nothing '物件釋放
Application.StatusBar = False
End Sub |
|
發表於 2014-6-6 12:22
| 
