- 帖子
- 234
- 主題
- 19
- 精華
- 0
- 積分
- 276
- 點名
- 0
- 作業系統
- Windows XP
- 軟體版本
- office 2003
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2013-1-7
- 最後登錄
- 2021-10-7
|
回復 1# PJChen
1.因為程式是執行Macro_T的Sheet,所以庫存表不會變更,將ActiveWorkbook改為WB
2.附件內程式的Workboos & sheets名稱與實際不符,將Macro.xlsm改為Macro_T.xlsm,VB改為VBA
試試看是否符合需求
Sub 群組設定版面()
Dim Wb As Workbook, xActSheet As Worksheet, j As String, xi As String, x As Double, i As Long
Dim k1 As String, k2 As String, k3 As String, k4 As String, k5 As String, k6 As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False '在程序執行過程中使出現的警告框不顯示
Application.Calculation = xlManual '手動計算
Set Wb = Workbooks(Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P1").Value) '儲存格的值為檔案名稱
j = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P2").Value
k1 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P3").Value
k2 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P4").Value
k3 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P5").Value
k4 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P6").Value
k5 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P7").Value
k6 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P8").Value
x1 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P9").Value
x2 = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P10").Value
x = Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P11").Value
For i = 2 To Wb.Sheets.Count '在工作表中循環
Wb.Sheets(i).Activate
With Wb.ActiveSheet.PageSetup
.PrintArea = j
.TopMargin = Application.InchesToPoints(k1) '上邊界
.BottomMargin = Application.InchesToPoints(k2) '下邊界
.LeftMargin = Application.InchesToPoints(k3) '左邊界
.RightMargin = Application.InchesToPoints(k4) '右邊界
.HeaderMargin = Application.InchesToPoints(k5) '頁首
.FooterMargin = Application.InchesToPoints(k6) '頁尾
.Zoom = x '列印縮放
.Orientation = xlLandscape '橫向列印
End With
Next
' End With
Application.ScreenUpdating = True '打開屏幕更新
Application.Calculation = xlAutomatic '自動計算
End Sub |
|
發表於 2020-2-5 13:28
| 