- ©«¤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 ©ó 2012-1-8 13:16 ½s¿è
¦^´_ 1# yagami12th
-> ¥Î§A³o¸Ìµo¤åªºÀÉ®× °õ¦æ¦¹µ{¦¡- Const ThePath = "d:\You\" '«ü©w¦s©ñªº¥D¸ê®Æ§¨
- Sub Ex()
- Dim d As Object, SavePath As String, Sh As Worksheet, R As Variant, E As Variant, Newbook As Workbook
- Dim MonPath As String, ¿ï¾ÜÅv As String, ¼i¬ù»ù As String
- Application.DisplayAlerts = False '°±¤î¨t²Î ªº´£¥Ü
- Application.ScreenUpdating = False '°±¤î¿Ã¹õ§ó·s¥\¯à
- Set d = CreateObject("scripting.Dictionary") '«Ø¥ß¦r¨åª«¥ó
- SavePath = Dir(ThePath, 16) '¶Ç¦^«ü©w¦s©ñªº¥D¸ê®Æ§¨
- If SavePath = "" Then MkDir (ThePath) '¦p¥D¸ê®Æ§¨¤£¦s¦b «Ø¥ß¥¦
- For Each Sh In Sheets
- d.RemoveAll '¦r¨åª«¥ó ²MªÅ¤lª«¥ó
- With Sh '¨Ì§Ç³B¸Ì ¨C¤@¤u§@ªí
- For Each R In .Range(.[D2], .[D2].End(xlDown)) '¨C¤@¤u§@ªí¤¤¦bdÄæ
- d(R.Value) = "" '¦r¨åª«¥ó ³]¥ß¤lª«¥ó(¼i¬ù»ù)
- Next
- MonPath = Mid(.[c2], 1, 4) & "_" & Mid(.[c2], 5) '¤ë¸ê®Æ§¨
- SavePath = Dir(ThePath & MonPath, 16) '´M§ä¤ë¸ê®Æ§¨
- If SavePath = "" Then MkDir (ThePath & MonPath) '¦p¤ë¸ê®Æ§¨¤£¦s¦b «Ø¥ß¥¦
- For Each E In Array("¶RÅv", "½æÅv") '¨Ì¿ï¾ÜÅv
- ¿ï¾ÜÅv = "\" & MonPath & IIf(E = "¶RÅv", "_C\", "_P\") '¤ë¸ê®Æ§¨\¿ï¾ÜÅv¸ê®Æ§¨
- SavePath = Dir(ThePath & MonPath & ¿ï¾ÜÅv, 16)
- If SavePath = "" Then MkDir (ThePath & MonPath & ¿ï¾ÜÅv)
- For Each R In d.KEYS '¦r¨åª«¥ó ¨Ì§Ç³B¸Ì¤lª«¥ó R (¼i¬ù»ù)
- .AutoFilterMode = False '¤u§@ªí¤¤¨ú®ø¦Û°Ê¿z¿ï
- .Range("A1").AutoFilter Field:=4, Criteria1:=R
- .Range("A1").AutoFilter Field:=5, Criteria1:=E
- 'AutoFilter ¤èªk[¦Û°Ê¿z¿ï] ¿z¿ï¥X¤@Ó²M³æ¡C
- 'Field:=4 ²Ä4Äæ (¼i¬ù»ù) ,Criteria1:=R ·Ç«h=R (¼i¬ù»ù)
- 'Field:=5 ²Ä5Äæ (¿ï¾ÜÅv) ,Criteria1:=E ·Ç«h=E (¿ï¾ÜÅv)
- ¼i¬ù»ù = Mid(.[c2], 1, 4) & "_" & Mid(.[c2], 5) & "_" & R & IIf(E = "¶RÅv", "_C", "_P")
- SavePath = ThePath & MonPath & ¿ï¾ÜÅv & ¼i¬ù»ù '¦sÀɪº§¹¾ã¸ô®|¦WºÙ
- Set Newbook = Workbooks.Add(1) '·s¶}ÀÉ®×(1¶)
- .UsedRange.SpecialCells(xlCellTypeConstants).Copy Newbook.Sheets(1).[a1]
- '¦Û°Ê¿z¿ïªº¸ê®Æ ½Æ»s¨ì·s¶}ÀɮײÄ1¶ªº.[a1]
- With Newbook.Sheets(1)
- .[O1] = "°ª»ù´î§C»ù"
- .[P1] = "¦¨¥æ¶qÅܤÆ"
- With .[O2].Resize(.UsedRange.Columns(1).Rows.Count - 1) '¦b³o½d³ò
- .Cells = "=RC[-8]-RC[-7]" 'µM«á¦bO2Äæ¦ì=g2-h2: ¨îq¤½¦¡
- .Value = .Value '¨úÈ -> ®ø°£¤½¦¡
- End With
- With .[P3].Resize(.UsedRange.Columns(1).Rows.Count - 2)
- .Cells = "=RC[-6]-R[-1]C[-6]" '¦bp3®æ¦ì¿é¤J¤½¦¡=j3-j2
- .Value = .Value
- End With
- End With
- Newbook.Close True, SavePath '·s¶}ÀÉ®×Ãö³¬ ¦sÀÉ
- Next
- Next
- .AutoFilterMode = False 'Â÷¶}¤u§@ªí«ì´_쪬
- End With
- Next
- Application.DisplayAlerts = True '«ì´_¨t²Îªº´£¥Ü
- Application.ScreenUpdating = True '¿Ã¹õ§ó·s¥\¯à¬O¶}±Òªº«h¬° True¡C
- MsgBox "¤u§@§¹¦¨"
- End Sub
½Æ»s¥N½X |
|