¦A½Ð±Ð¤@­ÓVBA»yªkªº°ÝÃD

skyutm

¦U¦ì¥ý¶i¤j®a¦n¡I
        ¤p§Ì¬O¦ì°ê¤p±Ð®v¡A·|·Q­n±µÄ²VBA¬O¦]¬°ªA°Èªº¾Ç®Õ¨Ã¨S¦³¦¨ÁZ­pºâ³nÅé¡A©Ò¥H¤p§Ìµo¼«¹Ï±j¡A¦Û´»°²¶}©l¬ãŪ¦³ÃöVBAªº®ÑÄy
       ¡A¥Ø«e¤w¬Ý§¹Excel VBA¶W¹Ï¸Ñ³o¥»®Ñ¡A¦ý¬O¦b¦¨ÁZ³nÅ骺³]­p¤W¤´µM§äµLÀYºü¡A©Ò¥H§Æ±æºô¸ô¤Wªº¦U¦ì¥ý¶i¥i¥H´£¨Ñ¸Ñ´bªºµª®×¡CÁÂÁ«ü±Ð¡I
        ­º¥ý¡A²Ä¤@­Ó°ÝÃD¬O¡G§Ú·Q­n¦bExcelªº²Ä¤@­Ó¤u§@ªí¤W³]©w¥i¥H¶ñ¤J¸ê®ÆªºªÅ®æ¡A¸ê®ÆÄ榳¤@­ÓªÅ¦ì¬O¶ñ¤H¼Æ¡A¦Ó©Ò¶ñ¤Jªº¤H¼Æ«h¥i¥H±±¨î²Ä¤G­Ó¤u§@ªíÀx¦s         
         ®æªº¼Æ¶q¡A¡]·N«ä´N¬O°ò¥»Äæ¶ñ¤J20¤H¡A¨º²Ä¤G­Ó¤u§@ªí«h·|¦C¥X20­ÓªÅ®æ¥H¶ñ¤J¾Ç¥Í©m¦W¡^¡A½Ð°Ý¸Ó§Q¥Î­þ¨Ç«ü¥O©Î¨ç¼Æ¡C·PÁ¸Ѵb¡I

skyutm

«z¡I¯u¬O¼ö¤ß§U¤H¥B¨³³t½T¹ê¡A·PÁ¤j¤j¸Ñµª¡A¤p§Ì¤w¤U¸üÀÉ®×´ú¸ÕµL»~¡C½Ðµ¥§Ú¥´¶}½s¿è¾¹¦n¦n¬ã¨s¤@¤U«ü¥O¡C¦³°ÝÃD¦A¨Ó½Ð±Ð¡A¥ýÁ°աI

skyutm

¦U¦ì¥ý¶i¤j®a¦n¡I
       «D±`·PÁ¤j®aªº¨ó§U¡C¤p§Ì©|¦³²Ä¤G­Ó°ÝÃD·Q­n½Ð±Ð¡C°ÝÃD¦p¤U¡G
       1.²Ä¤@­Ó¤u§@ªí¬O³]©wÄæ¡B²Ä¤G­Ó¤u§@ªí¬Oµn°O¦¨ÁZÄæ¦pªG¦b³]©wÄæ¶ñ¤J¦~¬q¡B¯Z¯Å¡B¤H¼Æ¡C¨Ò¦p 3¦~6¯Z15¤H¡B¦¨ÁZÄæ¦ì´N·|¥X²{30601~30615ªº15­ÓÄæ¦ì
       §Ú¸Õ¤F¤@¬q®É¶¡¡C¼Æ¦r¤W¥u¯à¥X²{1¡BµLªk¥X²{01¡A¥Ø«e§Ú¤w·Q¥X¦p¤Uªº»yªk¡A¦ý¬OµLªk°õ¦æ¡A½Ð¤j®a¸Ñ´b¤@¤U¡A¯u¬O·P¿E¡G
Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$b$3" Then
    Dim i As Integer
    For i = 1 To Target.Value
    Worksheets("sheet2").Cells(i + 2, "a") = i
    Next Target.Value
    End If
End Sub

skyutm

·PÁ¤j¤j¸Ñ´b¡A¤p§Ì¥ý¥h¬ã¨s¤@¤U¡C¦A¬Ý±¡§Î¦p¦ó¡C¥ýÁÂÅo¡I

skyutm

·PÁÂwhite945¤j¤jªº¦^ÂСA¦ý¬O¦³¤@­Ó¤p°ÝÃD¡A¦]¬°[B1:B3]¬O¥ý¶ñB1©Ò¥H·|¥X²{°}¦C¶W¥X¯Á¤Þ½d³òªº¿ù»~°T®§¡A§Ú²q¥i¯à¬O¦]¬°B3ÁÙ¨S¶ñ¼Æ¦r   ReDim ar(1 To [B3])
¥t¥~ÁÙ¦³
©Ó»X¤j®aªºÀ°§U¡A§Ú§â«e­±¨â¦ì¦n¤ß¤Hªº»yªk¥[¥H­×§ï«á¡AÅܦ¨¤U¦C¨â¦¡¦ý¬O¦A§â¥¦­Ì©ñ¦b²Ä¤@­Ó¤u§@ªíªº®É­Ô«o·|¥X²{°ÝÃD¡A·Q½Ð°Ý½×¾Âªº¤j¤j¡A°ÝÃD¬O¥X¦b­þ¸Ì¡A·PÁ«ü±Ð¡I
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$J$3" Then
        If Sheet2.Range("A1048576").EntireRow.Hidden Then
            Sheet2.Cells.EntireRow.Hidden = False
            Sheet2.Cells.EntireColumn.Hidden = False
        End If
        Sheet2.Range(Sheet2.Range("A" & Target.Value + 3), Sheet2.Range("A3").End(xlDown)).EntireRow.Hidden = True
        Sheet2.Range(Sheet2.Range("W1"), Sheet2.Range("W1").End(xlToRight)).EntireColumn.Hidden = True
    End If
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [J1:J3]) Is Nothing Then Exit Sub
    ReDim ar(1 To [J3])
    YC = [J1] & Format([J2], "00")
    For i = 1 To [J3]
    ar(i) = YC & Format(i, "00")
    Next
    Sheets(2).[A:A] = ""
    Sheets(2).[A3].Resize([J3], 1) = Application.Transpose(ar)
End Sub

skyutm

·PÁÂwhite945¤j¤jªº¦^ÂСA§Ú¥ý¥h°µ¤@¤U¥\½Ò¡A¦³°ÝÃD¦A¨Ó½Ð±Ð¡A¥ýÁÂÁ¤F¡I

skyutm

¤j®a¦n¡I¤S­n³Â·Ð¦U¦ì¥ý¶i¤F¡C¤p§Ìªº¶i«×¹J¨ì§xÃø¡C°ÝÃD¬O§Ú·Q§â´Á¤¤µû¶q¡]sheet2¡^ªºb3Äæ¦ìªº¦W¦r½Æ»s¨ì´Á¤¤¦¨ÁZ¡]sheet3¡^ªºb5Äæ¦ì¡C»yªk¦p¤U¡G

Worksheets("´Á¤¤µû¶q").Range(cells(3, 2), cells(3, 2).End(xlDown)).Copy
    ActiveSheet.Paste Destination:=Worksheets("´Á¤¤¦¨ÁZ").Range(cells(5, 2), cells(5, 2).End(xlDown))

¥i¬O¤@ª½µLªk°õ¦æ¡A¥i§_À°¦£¸Ñ¨M¡AÁÂÁ¡I

skyutm

·PÁª©¥D¤j¤jªº¸Ñ´b¡A¤p§Ì¥ý¥h´ú¸Õ¬Ý¬Ý¡C¥ýÁ°աI¡]¯u¬O´N·P¤ßªº½×¾Â¡^