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
