ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA_¦p¦ó¥HŪ¨úªº¡i³æÄæ¡j¦U²£¥Í¤@­Ó¤u§@ªí¡H

[µo°Ý] VBA_¦p¦ó¥HŪ¨úªº¡i³æÄæ¡j¦U²£¥Í¤@­Ó¤u§@ªí¡H

¥»©«³Ì«á¥Ñ Airman ©ó 2016-1-14 13:44 ½s¿è

°Ñ¦Òªþ¥ó¡Jhttp://www.FunP.Net/890930

¦C229~¦C249ªºµ{¦¡½X¬O¥HŪ¨úÁ`ªí(S_49_A¥æ¶°_" & NUM & "_" & mthcount & "´Á_ML)
TÄæ¨ìIVÄæ²Ä7¦C(§t)¥H¤U<>ªÅ¥ÕªºÀx¦s®æ¦U²£¥Í¤@­Ó¤u§@ªí~§Y¥H¦³Åã¥Ü¼Æ¦rªº¡iÀx¦s®æ¡j¬°¼Ð·Ç~
EX¡JÁ`ªíªºSheet24
S_49_A¥æ¶°_10_100´Á-24sheet-58(NOW) & S_49_A¥æ¶°_10_100´Á-24sheet-79(NOW)

½Ð°Ý¡J
¤U¦Cªºµ{¦¡½X­n¦p¦ó§ï¬°±NTÄæ¨ìIVÄæ²Ä7¦C(§t)¥H¤U¦³<>ªÅ¥ÕªºÀx¦s®æ¤§¦UÄæ¦U²£¥Í¤@­Ó¤u§@ªí~§Y¥H¦³Åã¥Ü¼Æ¦rªº¡i³æÄæ¡j¬°¼Ð·Ç~
EX¡JÁ`ªíªºSheet24
S_49_A¥æ¶°_10_100´Á-24sheet-58(NEW) & S_49_A¥æ¶°_10_100´Á-24sheet-79(NEW)

'²£¥Í¤u§@ªí..............................................................................      '¦C229
        Sa = 0
        Windows("S_49_A¥æ¶°_" & NUM & "_" & mthcount & "´Á_ML.xls").Activate  '¿ï¨úÁ`ªí
        shcount = ActiveWorkbook.Sheets.Count
        For sh = 1 To shcount  'Á`ªí¦U¤u§@ªí
        tx = Sheets(1).[R7].End(xlDown).Row 'RÄæ²Ä7¦C¥H¤U
        ty = Sheets(1).[IV5].End(xlToLeft).Column  'IVÄæ²Ä5¦C©¹¥ª
        
        For tz = 20 To ty 'TÄæ¨ìIVÄæ
        Sheets(1).Range("T7:T" & tx).Select 'TÄæ
        For Each b In Selection
            If b <> "" Then
                Sheets.Add  'TÄæ²Ä7¦C(§t)¥H¤U<>ªÅ¥ÕªºÀx¦s®æ²£¥Í¤u§@ªí
                ActiveWindow.Zoom = 75 'ÁY©ñ
                d = d + 1
                ReDim Preserve brr(d - 1)
                ActiveSheet.Name = Sheets(d + 1).[T5] & "-" & Sheets(d + 1).Range("R" & b.Row) '¤u§@ªí­«·s©R¦W
                brr(d - 1) = Sheets(d + 1).[T5] & "-" & Sheets(d + 1).Range("R" & b.Row) '¤u§@ªí­«·s©R¦W°O¿ý°}¦C
                'ActiveSheet.Name = Sheets(d + 1).[T5] & "-" & Sheets(d + 1).[T3] '¤u§@ªí­«·s©R¦W
                'brr(d - 1) = Sheets(d + 1).[T5] & "-" & Sheets(d + 1).[T3] '¤u§@ªí­«·s©R¦W°O¿ý°}¦C
   'I:T...............................................................................................................      '¦C249

¨ä¾l¸Ô¦pªþ¥ó¡C
¥H¤W  Àµ½Ð¦U¦ì¥ý¶i¡B«e½ú¤£§[½ç±Ð!  ÁÂÁÂ!

¥u´£¨Ñ´ú¸Õ¤ù¬q¡A°õ¦æ«e¡A¥ý¶}±Ò¡eS_49_A¥æ¶°_10_100´Á_ML¡fÀɮסã¡ã
¡@
S_49_A¥æ¶°(MF)-Q_v01.rar (64.3 KB)
¡@
¦Û¦æ¥h®M­×¡A³o¼Ëµ{¦¡´ê¦¨°ï¡A²´·ú¯uªº¦Y¤O¡I¡I¡I

TOP

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

­ã¤j¡J
¤£¦n·N«ä¡AÁ`¬O³Ò·Ð±z

¥H¶QÀÉ­ì¤å¥ý´ú¸Õ~«ö°õ¦æÁä«á§Y¡i²£¥Í°}¦C¯Á¤Þ¶W¥X½d³ò¡jªº´£¥Ü~°»¿ùµ²ªG¥d¦b¦C239¡C
¤£ª¾¬O¤£¬O¤p§Ìªº¾Þ§@¦³»~?
·q½Ð¦A¦¸½ç±Ð!ÁÂÁ±z

¥t~¤p§Ì¦³¦Û¦æ¸Õ­×¹L~¦ý¤£ª¾¦p¦óªí¹F¡iÁ`ªíªºTÄæ¨ìIVÄæ²Ä7¦C(§t)¥H¤U¦³<>ªÅ¥ÕªºÀx¦s®æ¤§¦UÄæ¡j~
¬õ¦â¦r½X·q½Ð½ç¥¿~·P®¦

'²£¥Í¤u§@ªí..............................................................................      '¦C229
        Sa = 0
        Windows("S_49_A¥æ¶°_" & NUM & "_" & mthcount & "´Á_ML.xls").Activate
        shcount = ActiveWorkbook.Sheets.Count
        For sh = 1 To shcount
        tx = Sheets(1).[R7].End(xlDown).Row
        ty = Sheets(1).[IV5].End(xlToLeft).Column
        
        For tz = 20 To ty
        Sheets(1).Range("T7:T" & tx).Select
        If Application.Count(Selection) > 0 Then
            Sheets.Add
            ActiveWindow.Zoom = 75 'ÁY©ñ
            d = d + 1
            ReDim Preserve brr(d - 1)
            ActiveSheet.Name = Sheets(d + 1).[T5] & "-" & Sheets(d + 1).[T3] '¤u§@ªí­«·s©R¦W
            brr(d - 1) = Sheets(d + 1).[T5] & "-" & Sheets(d + 1).[T3] '¤u§@ªí­«·s©R¦W°O¿ý°}¦C
            brr(d - 1) = ActiveSheet.Name
            Sheets(d + 1).Columns("A:T").Copy   '½Æ»sA:Tªº¸ê®Æ¤º®e
            Sheets(1).[A1].Select
            ActiveSheet.Paste
        End If
        Sheets(1).Range("T7:T" & tx).Select
        For Each b In Selection
            If b <> "" Then
    'I:T...............................................................................................................

PS:
¬õ¦â¦r½X­×¥¿«á¡A¤p§Ì´ú¸Õ«á~¦A¦V±z³ø§iµ²ªG~¦p­þÃäÁÙ¬O¦³°ÝÃD¦A­×~
³o¼Ë¤@¨B¨B¨Ó¡A¥i¯à¤ñ¸û¤£·|¤Ó³Ò·Ð±z

TOP

¦^´_ 3# Airman


°õ¦æ«e¡A¥ý¶}±Ò¡eS_49_A¥æ¶°_10_100´Á_ML¡fÀɮס@

·s¸ê®Æ§¨.rar (92.21 KB)
¡@
¨ä¥¦µù¸Ñ±¼ªºµ{¦¡½X¡Aªí¥Ü¥¼³B²z¡A¤Ó¦h¤F¡AµLªk¤@¤@¥h²z¸Ñ¡A
¤×¨ä´ú¸Õ®É¡A¤@ª½­«Âв£¥Í¤u§@ªíªº¬yµ{¡A¤Óªá®É¶¡¡I©Ò¥H²¤¥h¡I
¥Ø«e¥u¯à´£¨Ñ¦¹¬qµ{¦¡¡A½Ð¦Û¦æ¥h®M¤J¤Î­×§ï¡I

¤£¤Ó²ßºD±N¤£¦P»Ý¨D¡D¤£¦P¬yµ{ªºµ{¦¡¦ê¦b¤@°_¡A¹ï¾\Ū¤Î´ú¸Õ¬Û·í§xÂZ¤Î¶O®É¡A
¤@¯ë³£¨Ï¥Î¼Ò²Õ¤º¡A¥H°Ï°ì¤À¬q¼g¡A¦A¹B¥Îcall¤è¦¡¥h¦ê±µ¦U¬q¡]¦Û¤v¥Îªºµ{¦¡¡A¼g¦b¤u§@ªí¨Æ¥ó°Ïªº¤Ö¤§¤S¤Ö¡^¡ã¡ã

µø¤O¨ü·l¦Ü¤µ¥¼¦^´_¡A¥u¯à«j±j¬°¤§¡A®£µLªk´£¨Ñ¥þ­±§¹¾ãªºµ{¦¡½X¡I¡I¡I

TOP

¥»©«³Ì«á¥Ñ Airman ©ó 2016-1-15 22:57 ½s¿è

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

­ã¤j¡J
¤£¦n·N«ä¡A´ú¸Õµ²ªG~¶Q¸ÑµªÀɲ£¥Í±o®ÄªGÀɤΨ䤺ªº¤u§@ªí©M­ì´£°Ý¥DÀɲ£¥Íªº¤@¼Ò¤@¼Ë~
§Y»PS_49_A¥æ¶°_10_100´Á-24sheet-58(NOW) & S_49_A¥æ¶°_10_100´Á-24sheet-79(NOW)§¹¥þ¬Û¦P¡C

¤p§Ìªº»Ý¨D¬O®ÄªGÀɤ¤ªº¤u§@ªí­n¦pS_49_A¥æ¶°_10_100´Á-24sheet-58(NEW) & S_49_A¥æ¶°_10_100´Á-24sheet-79(NEW)¤@¯ë~
¥t~
¤u§@ªí¦WºÙ½Ð§ï¬°¡J
                ActiveSheet.Name = Sheets(d + 1).[T5] & "-" & Sheets(d + 1).[T3]  '¤u§@ªí­«·s©R¦W
                brr(d - 1) = Sheets(d + 1).[T5] & "-" & Sheets(d + 1) .[T3] '¤u§@ªí­«·s©R¦W°O¿ý°}¦C
¥H¤W  Àµ½Ð´f¤©½ç±Ð!ÁÂÁ±z

¦pªG¯uªº¬O¤Ó³Â·Ð~
¥i§_¥ý½Ð±z½ç±Ð¡J
ªí¹F¡iÁ`ªíªºTÄæ¨ìIVÄæ²Ä7¦C(§t)¥H¤U¦³<>ªÅ¥ÕªºÀx¦s®æ¤§¦UÄæ¡jªº½d³ò¤§»yªk~
§Y«e­z3# ¬õ¦â¦r½Xªº¥¿½T»yªk~
·P®¦

TOP

¦^´_ 5# Airman


Sheets(1).Range("T7:T" & tx).Select

¸Õ¸Õ¡×¡Ö¡@Sheets(1).Cells(7, tz).Resize(tx - 6).Select¡@¡@


¡eNOW¡f¡eNEW¡f¤£¬O¹ï¤ñ¡A«ÜÃø§PÂ_­þ­Ó¬°·s»Ý¨D¡H

¥Õ¥Õªá¤F¥b¤Ñ®É¶¡¡I«¢¡ã¡ã

TOP

¦^´_ 6# ­ã´£³¡ªL
­ã¤j:
¨þ~¨þ~¬Ý¨Ó§Ú¤]»~¸ÑAª¾ÃѪøªºµ{¦¡½X~
¤p§Ì­ì¥H¬°¥u­n±NTÄæ½d³òªºµ{¦¡½X~Sheets(1).Range("T7:T" & tx).Select
§ï¬°:
Á`ªí¦U³æÄ檺½d³ò~ Sheets(1).Cells(7, tz).Resize(tx - 6).Select
À³¸Ó´N¥i¥H¤F~
¦ý¶]¥X¨Óªºµ²ªG§¹¥þ¤£¹ï

³o¤U¤p§Ì¹ê¦b¨S»³¤F

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD