- ©«¤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
        
|
¦^´_ 2# iceandy6150 - Option Explicit
- Sub Ex()
- Dim i As Integer, Sh As Worksheet
- Application.DisplayAlerts = False
- For Each Sh In Sheets
- If Sh.Name <> "¤u§@ªí1" And Sh.Name <> "¤u§@ªí2" Then Sh.Delete
- '¬¡¶Ã¯¥u¯d ¤u§@ªí1¡G¬O¿é¤J°Ï,¤u§@ªí2¡G¬O¾ú¥v°O¿ý
- Next
- With Sheets("¤u§@ªí2")
- If .UsedRange.Rows.Count = 1 Then '¨S¦³¾ú¥v¬ö¿ý
- Sheets("¤u§@ªí1").UsedRange.Copy '½Æ»s(§t¼ÐÀY)
- .Range("a1").PasteSpecial xlPasteValues
- Else
- Sheets("¤u§@ªí1").UsedRange.Offset(1).Copy '½Æ»s(¤£§t¼ÐÀY)
- .Range("A" & .UsedRange.Rows.Count).Offset(3).PasteSpecial xlPasteValues
- 'Offset(3) :ªÅ2¦C->²Ä3¦C¶K¤W
- End If
- .UsedRange.Range("E:E").AdvancedFilter xlFilterCopy, , .Cells(1, .Columns.Count), True
- '¶i¶¥¿z¿ï EÄæ ¤£«½Æ¸ê®Æ¨ì¤u§@ªí³Ì¥kÄæ ***¨ú±oÃþ§Oªº¤ÀÃþ***
- i = 2
- .Cells(1, .Columns.Count - 1) = .UsedRange.Range("E1") '¶i¶¥¿z¿ïªºÄæ¦ì¦WºÙ¬OEÄ檺¼ÐÀY
- Do While .Cells(i, .Columns.Count) <> "" '¤u§@ªí³Ì¥kÄ檺Àx¦s®æ <>""
- Set Sh = Sheets.Add(, Sheets(Sheets.Count)) '·s¼WªºÃþ§O¤u§@ªí
- Sh.Name = .Cells(i, .Columns.Count)
- .Cells(2, .Columns.Count - 1) = Sh.Name
- .UsedRange.AdvancedFilter xlFilterCopy, .Cells(1, .Columns.Count - 1).Resize(2), Sh.[a1], True
- '¤u§@ªí2 ¶i¶¥¿z¿ï ·Ç«h[¤ÀÃþ]="¤u§@ªí1"³Ì¥kÄ檺Àx¦s®æ, ½Æ»s¨ì·s¼WªºÃþ§O¤u§@ªí[A1]
- Do While Sh.UsedRange.Rows.Count > 11 '¸ê®Æ¦C>11¦C
- Sh.Copy , Sheets(Sh.Index) '1 ì¤u§@ªí½Æ»s
- Sh.Rows("11:" & Sh.Rows.Count).Delete '2 ì¤u§@ªí§R°£11¦C¥H¤Uªº¸ê®Æ(«O«ù10¦C)
- Set Sh = ActiveSheet '3 ½Æ»sªº¤u§@ªí «ü©wµ¹ÅܼÆ
- Sh.Rows("2:11").Delete '4 ½Æ»sªº¤u§@ªí§R°£2:11¦Cªº¸ê®Æ
- Loop
- i = i + 1
- Loop
- .Cells(1, .Columns.Count).CurrentRegion = ""
- End With
- End Sub
½Æ»s¥N½X |
|