[µo°Ý] ¸s²Õ³]©wª©­±

PJChen

Dear,
§Ú¦bWorkbooks("Macro_T.xlsm").Worksheets("VBA").Range("P1")«ü©w¤@­Ó¤u§@ªí¦WºÙ
¥Î¨Ó³]©w³o­ÓÀɮתº©Ò¦³Sheet,¨Ïª©­±¤@­P
¦ý³]©w³£¤£¦¨¥\,½Ð°Ý¬O­þ­Ó»yªk¿ù»~?

1. Sub ¸s²Õ³]©wª©­±()
   
2. Dim Wb As Workbook, xActSheet As Worksheet, j As String, xi As String, x As Double, i As Long
   
3. Dim k1 As String, k2 As String, k3 As String, k4 As String, k5 As String, k6 As String
   
4.     Application.ScreenUpdating = False
   
5.     Application.DisplayAlerts = False '¦bµ{§Ç°õ¦æ¹Lµ{¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
   
6.     Application.Calculation = xlManual     '¤â°Ê­pºâ
   
7.     Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VB").Range("P1").Value) 'Àx¦s®æªº­È¬°ÀɮצWºÙ
   
8. 
   
9.     j = Workbooks("Macro.xlsm").Worksheets("VB").Range("P2").Value
   
10.     k1 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P3").Value
    
11.     k2 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P4").Value
    
12.     k3 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P5").Value
    
13.     k4 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P6").Value
    
14.     k5 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P7").Value
    
15.     k6 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P8").Value
    
16.     x1 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P9").Value
    
17.     x2 = Workbooks("Macro.xlsm").Worksheets("VB").Range("P10").Value
    
18.     x = Workbooks("Macro.xlsm").Worksheets("VB").Range("P11").Value
    
19.    
    
20.     With Wb.ActiveSheet.PageSetup
    
21.         For i = 2 To ActiveWorkbook.Sheets.Count '¦b¤u§@ªí¤¤´`Àô
    
22.         ActiveWorkbook.Sheets(i).Activate
    
23. 
    
24.          ActiveSheet.PageSetup.PrintArea = j
    
25.         .TopMargin = Application.InchesToPoints(k1)   '¤WÃä¬É
    
26.         .BottomMargin = Application.InchesToPoints(k2)  '¤UÃä¬É
    
27.         .LeftMargin = Application.InchesToPoints(k3)  '¥ªÃä¬É
    
28.         .RightMargin = Application.InchesToPoints(k4)  '¥kÃä¬É
    
29.         .HeaderMargin = Application.InchesToPoints(k5) '­¶­º
    
30.         .FooterMargin = Application.InchesToPoints(k6)  '­¶§À
    
31. 
    
32.         .Zoom = x '¦C¦LÁY©ñ
    
33.         .Orientation = xlLandscape   '¾î¦V¦C¦L
    
34.         Next
    
35.     End With
    
36.         Application.ScreenUpdating = True   '¥´¶}«Ì¹õ§ó·s
    
37.         Application.Calculation = xlAutomatic    '¦Û°Ê­pºâ
    
38.    
    
39. End Sub

½Æ»s¥N½X

¸s²Õª©­±A.rar (260.99 KB)

¤U¸ü¦¸¼Æ: 5

2020-2-4 19:44

jcchiang

¦^´_ 1# PJChen
1.¦]¬°µ{¦¡¬O°õ¦æMacro_TªºSheet,©Ò¥H®w¦sªí¤£·|Åܧó,±NActiveWorkbook§ï¬°WB
2.ªþ¥ó¤ºµ{¦¡ªºWorkboos & sheets¦WºÙ»P¹ê»Ú¤£²Å,±NMacro.xlsm§ï¬°Macro_T.xlsm,VB§ï¬°VBA
¸Õ¸Õ¬Ý¬O§_²Å¦X»Ý¨D

Sub ¸s²Õ³]©wª©­±()
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 '¦bµ{§Ç°õ¦æ¹Lµ{¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
    Application.Calculation = xlManual     '¤â°Ê­pºâ
    Set Wb = Workbooks(Workbooks("Macro_T.xlsm").Worksheets("VBA").Range("P1").Value) 'Àx¦s®æªº­È¬°ÀɮצWºÙ

    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   '¦b¤u§@ªí¤¤´`Àô
        Wb.Sheets(i).Activate   

      With Wb.ActiveSheet.PageSetup
        .PrintArea = j
        .TopMargin = Application.InchesToPoints(k1)   '¤WÃä¬É
        .BottomMargin = Application.InchesToPoints(k2)  '¤UÃä¬É
        .LeftMargin = Application.InchesToPoints(k3)  '¥ªÃä¬É
        .RightMargin = Application.InchesToPoints(k4)  '¥kÃä¬É
        .HeaderMargin = Application.InchesToPoints(k5) '­¶­º
        .FooterMargin = Application.InchesToPoints(k6)  '­¶§À

        .Zoom = x '¦C¦LÁY©ñ
        .Orientation = xlLandscape   '¾î¦V¦C¦L
     End With
        Next
   ' End With
        Application.ScreenUpdating = True   '¥´¶}«Ì¹õ§ó·s
        Application.Calculation = xlAutomatic    '¦Û°Ê­pºâ
   
End Sub

PJChen

¦^´_ 2# jcchiang

·PÁ±z,
´ú¸ÕOK¤F,
½Ð°Ý
1) ¥H¤U¥]°_¨Óªº³o­Ó­n«ç»ò°Ï¤À, For/Withªº¶¶§Ç?§Ú¤À¤£²M·¡....
2) §Ú¦³¥t¥~¤@­Ó°ÝÃDhttp://forum.twbts.com/viewthrea ... a=pageD1&page=2
Ãö©ó "§R°£¤j©ó¤ë©³¤éªº¤u§@ªí"¤@ª½§xÂZµÛ§Ú,¯à§_¤]À°¦£¬Ý¤U»yªk,¨º­Óµ{¦¡¥u¦³³o­Ó³¡¥÷¤£¦¨¥\,¨ä¥L³£¨S°ÝÃD
«ô°U¤F....

1.         For i = 2 To Wb.Sheets.Count '¦b¤u§@ªí¤¤´`Àô
   
2.         Wb.Sheets(i).Activate
   
3.             With Wb.ActiveSheet.PageSetup
   
4. ¬Ù²¤....
   
5.             End With
   
6.                 Next

½Æ»s¥N½X

­ã´£³¡ªL

.TopMargin = Application.CentimetersToPoints(???)  
Ãä¬É¥H"¤½¤À"¬°³æ¦ì

jcchiang

¦^´_ 3# PJChen

°ÝÃD¤@:µ{¦¡±Æª©¨S°µ¦n,³y¦¨»~¸Ñ
For-Next¬O±q²Ä¤G­Ó¤u§@ªí¶}©l°õ¦æ,´`Àô¦ÜWb.Sheets.Count«á°±¤î
¨C¤@¦¸´`Àô¤¤°õ¦æWith-End With§@·~

For i = 2 To Wb.Sheets.Count '¦b¤u§@ªí¤¤´`Àô
      Wb.Sheets(i).Activate
      With Wb.ActiveSheet.PageSetup
¬Ù²¤....
     End With
Next
°ÝÃD¤G:³o­Ó°ÝÃD¤@¶}©l¤£²M·¡¥Î³~,¤§«á¬Ý¦³¥ý¶i¦^ÂÐ,´N¨S¦b¬ã¨s¤F,«áÄò¦³®É¶¡¦A¬ã¨s¬Ý¬Ý

PJChen

¦^´_ 4# ­ã´£³¡ªL

½Ð°Ý­ã¤j,

.TopMargin = Application.CentimetersToPoints(???)  
Ãä¬É¥H"¤½¤À"¬°³æ¦ì

©Ò¥H³o­Ó³¡¥÷¤£¯à«ü©w¦bÀx¦s®æ¤¤°õ¦æ ¬O¶Ü? ÁÙ¬O­n¤£¦Pªº»yªk?
    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

­ã´£³¡ªL

¦^´_ 6# PJChen


¦P¼Ë¥i¥ÎÀx¦s®æ­È¬°Ãä¬É­È,
¥u¤£¹L±NÀx¦s®æ­Èµø¬°"¤½¤À", ¦Ó¤£¬O"­^¦T"

PJChen

¦^´_ 7# ­ã´£³¡ªL

­ã¤j,
©Ò¥H..
Àx¦s®æªº­È*2.53¥ª¥k,¬Oª©­±³]©wªº­È

­ã´£³¡ªL

¦^´_ 8# PJChen

.TopMargin = Application.InchesToPoints(2)   Ãä¬É2­^¦T = 2*2.54 = 5.1¤½¤À
.TopMargin = Application.CentimetersToPoints(2)  Ãä¬É = 2¤½¤À

PJChen

¦^´_ 9# ­ã´£³¡ªL

­ì¨Ó¥i¥H¥Î¤£¦Pªº³æ¦ì
·PÁ»¡©ú