«ç¼Ë¤ÀÃþ«á¶K¤W¦U¦Ûªºsheet
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 24
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows XP
- ³nÅ骩¥»
- SP2
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- ¨Ó¦Û
- HK
- µù¥U®É¶¡
- 2013-2-21
- ³Ì«áµn¿ý
- 2021-9-16
|
«ç¼Ë¤ÀÃþ«á¶K¤W¦U¦Ûªºsheet
½Ð°Ý«ç¼Ë±Nexcelªº¤ëµ²³æ¸ê®Æ®Ú¾Ú¥kÃ䪺job number ¥Î VBA ¶K¦b¦U¦Ûªºsheet ¤W¡HÁ [¨£ªþ¥ó]
pls.¤p§Ì¥u¯à¥Î¿ý»sªº¥\¯à°µ, ¤£¹Lrun VBA®É¸g±`·|³B²z¤£¤F, ¦³¨S¦³Â²³æªº³B²z¤èªk=.= |
-
-
±N¸ê®Æ¤ÀJOB.rar
(11.36 KB)
±N¸ê®Æ¤ÀJOB
|
HERO
|
|
|
|
|
- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-4-13 15:55 ½s¿è
¦^´_ 1# Hero2013
¸Õ¸Õ¬Ý- Sub ¤ÀJob1()
- Dim DataBase As Range
- With Sheets("¤¤»È¤ä²¼")
- Set DataBase = .Range("a5").CurrentRegion
- 'CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪ
- .Cells(1, .Columns.Count) = DataBase.Cells(1, DataBase.Columns.Count) '¤u§@ªí³Ì«á¤@Äæ=DataBase³Ì«á¤@Ä檺¼Ð¦W(job number )
- DataBase.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
- 'AdvancedFilter(¶i¶¥¿z¿ï) ¤èªk °ò©ó·Ç«h½d³ò±q¸ê®Æ²M³æ¤¤¿z¿ï©Î½Æ»s¸ê®Æ¡C¦pªGªì©l¿ï©w¬°³æÓÀx¦s®æ¡A«h¨Ï¥ÎÀx¦s®æ¥Ø«eªº°Ï°ìx¬°Variant¡C
- i = 2
- .AutoFilterMode = False
- 'AutoFilterMode ÄÝ©Ê ©w¦pªG¥Ø«e¦b¤u§@ªí¤WÅã¥Ü¦³ [¦Û°Ê¿z¿ï] ¤U©Ô½b¸¹¡A«h¦¹Äݩʬ° True¡C¸ÓÄÝ©Ê»P FilterMode Äݩʤ¬¬Û¿W¥ß¡CŪ/¼g Boolean¡C
- '³Æµù ¦pªG¥Ø«eÅã¥Ü [¦Û°Ê¿z¿ï] ¤U©Ô½b¸¹¡A¦¹ÄݩʶǦ^ True¡C¥i±N¸ÓÄݩʳ]©w¬° False¡A¥H²¾°£¸Ó½b¸¹¡A¦ýµLªk±N¨ä³]©w¬° True¡C¥i¥Î AutoFilter ¤èªk¹ï¸ê®Æ²M³æ¶i¦æ¿z¿ï¨ÃÅã¥Ü¸Ó¤U©Ô½b¸¹¡C
- On Error GoTo Err_Sheet
- 'On Error ³¯z¦¡ ±Ò°Ê¤@Ó¿ù»~³B²z±`¦¡¡A¥B«ü©w¦¹±`¦¡¦b¤@Óµ{§Çùتº¦ì¸m¡C¤]¥i¥Î¨Ó°±¤î¤@Ó¿ù»~³B²z±`¦¡¡C
- Do While .Cells(i, .Columns.Count) <> "" '°õ¦æ¦^°éªº±ø¥ó:¤¤»È¤ä²¼³Ì«á¤@Ä檺i¦C <>""
- DataBase.AutoFilter Field:=DataBase.Columns.Count, Criteria1:=.Cells(i, .Columns.Count)
- 'AutoFilter ¤èªk ¨Ï¥Î [¦Û°Ê¿z¿ï] ¿z¿ï¥X¤@Ó²M³æ¡C¬° Variant¡C
- With Sheets(.Cells(i, .Columns.Count).Value).Range("a5")
- .CurrentRegion = ""
- DataBase.Copy .Cells
- End With
- i = i + 1
- Loop
- .AutoFilterMode = False
- .Columns(.Columns.Count) = "" '²M°£¶i¶¥¿z¿ï©ó³Ì«á¤@Ä檺¸ê®Æ
- End With
- Exit Sub
- Err_Sheet:
- If Err = 9 Then
- With Sheets("¤¤»È¤ä²¼")
- Sheets.Add , Sheets(Sheets.Count) '´¡¤J·sªº ¤u§@ªí
- ' .Copy , Sheets(Sheets.Count) '½Æ»s "¤¤»È¤ä²¼" ¤u§@ªí(®æ¦¡¦p ¤¤»È¤ä²¼)
- ActiveSheet.Name = .Cells(i, .Columns.Count) '¨î©w·s¤u§@ªíªº¦WºÙ
- ' ActiveSheet.AutoFilterMode = False '½Æ»s "¤¤»È¤ä²¼" »ÝÅã¥Ü©Ò¦³¸ê®Æ
- End With
- Resume
- 'Resume ³¯z¦¡ ¦b¿ù»~³B²z±`¦¡µ²§ô«á , «ì´_즳ªº°õ¦æ
- Else
- MsgBox "µ{¦¡¿ù»~ ¿ù»~½X" & vbLf & Err
- End If
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 24
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows XP
- ³nÅ骩¥»
- SP2
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- ¨Ó¦Û
- HK
- µù¥U®É¶¡
- 2013-2-21
- ³Ì«áµn¿ý
- 2021-9-16
|
GBKEEªO¥D, ¥i¥H¹B§@¤F, ¯uªºÁÂÁ§A¡C
§xÂZ¤p§Ìªº°ÝÃD, ¤@¤U¤l¸Ñ¨M¤F^^ |
|
HERO
|
|
|
|
|