jackson7015

¦^´_ 4# yen956
¦^´_ 5# GBKEE

·PÁª©¤j©M«e½ú­Ìªº¨ó§U
¤µ¤Ñ¬ã¨s¤F¤@¤Ñ
·M¶wªº§Ú¤@ª½¤£¾å±o¸Ó§âµ{¦¡ªk©ñ¦b­þ­Ó¦a¤è
¦³©ñ¸m¨ì¤u§@ªí³æ1¤ºªº®É­Ô¡A¥i¥H°Ê§@
¦ý¬O³y¦¨ÂIÀ»¨ì[B3]´N·|°õ¦æµ{¦¡½X
¨ä¥L¸Õ¹L©ñ¦bª«¥ó¡Bªí³æ¡B¼Ò²Õ..¤ºªºµ{¦¡½X³£¨S¦³¤ÏÀ³
¥Bµ{¦¡½X°õ¦æ®É¡AµLªk¹wÄý¦C¦L
¥u¦³ª½±µ¦C¦L¥X¨Ó¤~¯àª¾¹D¬O§_¥¿½T(ªá¤FÆZ¦h±i¯Èªº...)

¬O§_«ü¾É¤p§Ì¸Ó¥¿½T©ñ¦b¦ó³B...·P¿E¤£ºÉ...:L

GBKEE

¦^´_ 3# jackson7015

1. '¤ä²¼ ¤u§@ªí¼Ò²Õ ªºµ{¦¡½X
   
2. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
3.     If Target.Address = "$B$3" And [B3] > 0 Then
   
4.        If MsgBox("¦L¦C ¤ä²¼ " & Target & "µ§", vbYesNo + vbInformation) = vbYes Then
   
5.             Sheet_PageSetup
   
6.             Sheet_PrintOut
   
7.        End If
   
8.     End If
   
9. End Sub
   
10. Private Sub Sheet_PageSetup()      '¦L¦Cªº³]©w
    
11.     With PageSetup
    
12.         .PrintArea = "$I$44:$W$65"  '¦L¦C½d³ò   "¤@±i¤ä²¼ªº½d³ò"
    
13.         .CenterHorizontally = True  '¦pªG¦C¦L®É­¶­±¤Wªº¤u§@ªí¬O¤ô¥­¸m¤¤ªº«h¬° True
    
14.         .CenterVertically = True    '¦pªG¦C¦L®É­¶­±¤Wªº¤u§@ªí¬O««ª½¸m¤¤ªº«h¬° True
    
15.         .PaperSize = xlPaperB4      '³]©w¯È±i¤j¤p  '¥i¬ÝPaperSize »¡©ú
    
16.         .FitToPagesWide = 1         '³]©w­¶¼e=1­¶
    
17.         .FitToPagesTall = 1         '³]©w­¶°ª=1­¶
    
18.     End With
    
19. End Sub
    
20. Private Sub Sheet_PrintOut()
    
21.     For I = 1 To [B3]
    
22.         [L50] = Cells(I + 6, "C")
    
23.         [M50] = Cells(I + 6, "D")
    
24.         [O50] = Cells(I + 6, "E")
    
25.         [P50] = Cells(I + 6, "F")
    
26.         [Q50] = Cells(I + 6, "G")
    
27.         PrintOut Copies:=5    '"¤@±i¤ä²¼ªº½d³ò"  ¦L¦C ¤­Áp³æ
    
28.     Next
    
29. End Sub

½Æ»s¥N½X

yen956

¦^´_ 3# jackson7015
¸Õ¸Õ¬Ý:

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     Dim r1, r2 As Integer, rng As Range
   
3.     Set rng = Worksheets("¤ä²¼").[B3]
   
4.     If Not Intersect(Target, rng) Is Nothing Then
   
5.         For i = 1 To [B3]
   
6.             r1 = i * 22 + 22: r2 = i * 22 + 43
   
7.             ActiveSheet.PageSetup.PrintArea = _
   
8.                  "$I$" & r1 & ":$W$" & r2 & ",$Y$" & r1 & ":$AM$" & r2 & ",$AO$" & r1 & ":$BC$" & r2 _
   
9.                        & ",$BE$" & r1 & ":$BS$" & r2 & ",$BU$" & r1 & ":$CI$" & r2
   
10.         Next
    
11.     End If
    
12. End Sub

½Æ»s¥N½X

jackson7015

¦^´_ 2# yen956

·PÁÂyen956«e½úªº«ü¾É

·Q½Ð±Ðªº¬O¦C¦L©Ò¦³ªº³¡¤À
¬O§_¥i¥H§ó§ï¦¨[B3]=31ªº®É­Ô¦C¦L³¡¤À¬°1~31ªº¤­Áp³æ ?

¦]¬°¦n¹³·|²o¯A¨ì­¶¼Æ³¡¤À
´N¬O¦C¦L¥X¨Óªº1~5­¶¬O²Ä¤@¥÷¤­Áp³æ¡B6~10­¶¬°²Ä¤G¥÷¤­Áp³æ...Ãþ±À
©Ò¥H¤£¾å±o¸Ó§ó§ï­þ¨Ç³¡¤À

·Q½Ð¤j¤j­ÌÀ°¦£¤@¤U¡A·P¿E¤£ºÉ~

yen956

¦^´_ 1# jackson7015
¬O¤£¬O³o­Ó·N«ä?
[B3]¥X²{20, PrintArea ³]©w¬°¦C¦L ²Ä20®M ¤­³s³æ?

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     Dim r1, r2 As Integer, rng As Range
   
3.     Set rng = Worksheets("¤ä²¼").[B3]
   
4.     If Not Intersect(Target, rng) Is Nothing Then
   
5.         r1 = [B3] * 22 + 22: r2 = [B3] * 22 + 43
   
6.         ActiveSheet.PageSetup.PrintArea = _
   
7.              "$I$" & r1 & ":$W$" & r2 & ",$Y$" & r1 & ":$AM$" & r2 & ",$AO$" & r1 & ":$BC$" & r2 _
   
8.                    & ",$BE$" & r1 & ":$BS$" & r2 & ",$BU$" & r1 & ":$CI$" & r2
   
9.        ActiveSheet.PrintPreview    '¹wÄý¦C¦L
   
10.     End If
    
11. End Sub

½Æ»s¥N½X