Ãö©ó¼g¥¨¶°µ{¦¡¦Û°Ê¿z¿ï§PÂ_°Ïªº¥N½X½Æ»s¦¨¸Ó¥N½X³æ¿W¬¡­¶Ã¯

¾Ç¨ì¦Ñ¦º

¦U¦ì¤j¤j¦n,  ½d¨Ò¦pªþ¥ó

»¡©ú¤@¤U

¦³¤@­Ó·JÁ`ªíªº¬¡­¶Ã¯¡A¤pªº·Q­n³z¹L¥¨¶°¦Û°Ê¤Àªù§OÃþ¨ì¦U¦Ûªº¬¡­¶Ã¯¡C

PS.ªþ¥ó¤¤¡AA,B,Cªº¬¡­¶Ã¯¬O°õ¦æ¥¨¶°«á¡A²£¥Í¥X¨Óªºµ²ªG¡C


ÁÂÁ¦U¦ì¡C

yen956

¸Õ¸Õ¬Ý:

1. '½Ð¶K¨ì "·JÁ`ªí"
   
2. Sub ·J¤JÁ`ªí()
   
3.     Dim sh1 As Worksheet, sh2 As Worksheet
   
4.     Dim Lst1 As Integer, Lst2 As Integer
   
5.     Dim I As Integer, J As Integer
   
6.     Set sh1 = Sheets("·JÁ`ªí")
   
7.     For J = 1 To Sheets.Count
   
8.         If Sheets(J).Name <> "·JÁ`ªí" Then
   
9.             Set sh2 = Sheets(J)
   
10.             Lst1 = sh1.[B65536].End(xlUp).Row
    
11.             Lst2 = sh2.[B65536].End(xlUp).Row
    
12.             For I = 5 To Lst2
    
13.                 sh2.Cells(I, 2).Resize(1, 4).Copy sh1.Cells(Lst1 + I - 4, 2)
    
14.             Next
    
15.         End If
    
16.     Next
    
17. End Sub

½Æ»s¥N½X

¾Ç¨ì¦Ñ¦º

to yen956 ¤j¤j¡A§Ú°õ¦æ«á¡AµL¤ÏÀ³»¡¡A§Ú¤w¶K¨ì·JÁ`ªíªº¥¨¶°¤º¡C

yen956

¦^´_ 3# ¾Ç¨ì¦Ñ¦º
¥H¤U¾Þ§@«Y¥H 2003¬°¨Ò               
§Aªºª©¥»½Ð¦Û¦æ°Ñ¦Ò¡G               
1. «ö Alt+F11       
2. Double Click sheet("·JÁ`ªí")       
3. ¶K¤W VBA Code       
4. ÂI¥¨¶° Sub()¡Kend       
5. «ö F5       

¤U¸ü (460.44 KB)

2016-2-19 17:37

¾Ç¨ì¦Ñ¦º

yen956¤j¤j ·PÁ §Ú¦¨¥\ªº°õ¦æ¤F¥¨¶°¡A¦ý¬O¼g¤Ï¤F@o@ §Ú·Q­nªº¬O·JÁ`ªíªº¸ê®Æ¦Û°Ê¤ÀÃþ¦¨A,B,C...... ~O~

yen956

¦^´_ 5# ¾Ç¨ì¦Ñ¦º
'·J¥X¨ì¤À­¶
'¥ý¨M±ø¥ó:"·JÁ`ªí"ÄæB¤¤ªºsheets¥²¶·¦s¦b
Sub ·J¥X¨ì¤À­¶()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim Lst1 As Integer, Lst2 As Integer
    Dim I As Integer, J As Integer, shName As String
    Set sh1 = Sheets("·JÁ`ªí")
    Lst1 = sh1.[B65536].End(xlUp).Row
    For I = 5 To Lst1
        shName = sh1.Cells(I, 2)
        For J = 1 To Sheets.Count
            If Sheets(J).Name = shName Then
                Lst2 = Sheets(J).[B65536].End(xlUp).Row + 1
                If Lst2 < 5 Then Lst2 = 5
                sh1.Cells(I, 2).Resize(1, 4).Copy Sheets(J).Cells(Lst2, 2)
                Exit For
            End If
        Next
    Next
End Sub

c_c_lai

¦^´_ 5# ¾Ç¨ì¦Ñ¦º
®M¥Î yen956 ¤j¤jªº²{¦¨µ{¦¡¡G

1. '  ½Ð¶K¨ì "·JÁ`ªí"
   
2. Sub ·J¤JÁ`ªí()
   
3.     Dim sh1 As Worksheet, sh2 As Worksheet
   
4.     Dim Lst1 As Integer
   
5.     Dim J As Integer
   
6.    
   
7.     Set sh1 = Sheets("·JÁ`ªí")
   
8.     sh1.Cells.Clear
   
9.    
   
10.     For J = 1 To Sheets.Count
    
11.         If Sheets(J).Name <> "·JÁ`ªí" Then
    
12.             Set sh2 = Sheets(J)
    
13.             Lst1 = sh1.[B65536].End(xlUp).Row + 1
    
14.            '  sh2.UsedRange.Address = "$B$4:$E$7" : String
    
15.            '  sh2.UsedRange.Offset(1, 0).Address = "$B$5:$E$8" : String
    
16.            sh2.UsedRange.Offset(1, 0).Copy sh1.Cells(Lst1, 2)
    
17.         End If
    
18.     Next
    
19. End Sub

½Æ»s¥N½X

yen956

¦^´_ 7# c_c_lai
ÁÂÁÂc¤jªº«ü¾É!!
§ï¥Îc¤jªº UsedRange ¥i§å¦¸¶K¤W, ªGµM²±¶¦h¤F, ÁÂÁ«ü¥¿!!

yen956

'­Y¸ê®ÆÃe¤j, ·J¥X¸ê®Æ¨ì¤À­¶, ¥i§ï¥Î¥»VBA, ·|§Ö«Ü¦h
'¥ý¨M±ø¥ó:"·JÁ`ªí"ÄæB¤¤ªº¤u§@ªí¦WºÙªºsheets¥²¶·¦s¦b
'¥B¤w«ö¤u§@ªí¦WºÙ±Æ§Ç
Sub ·J¥X¨ì¤À­¶2()
    Dim sh1 As Worksheet
    Dim Lst1 As Integer, shNameCnt As Integer
    Dim I As Integer
    Set sh1 = Sheets("·JÁ`ªí")
    Lst1 = sh1.[B65536].End(xlUp).Row
    I = 5
    Do
        shName = sh1.Cells(I, 2)
        sh1.[C3].FormulaR1C1 = "=COUNTIF(C[-1],""=""&R" & I & "C[-1])"   '­pºâ¦P¦Wªº¤u§@ªí¦³´X­Ó
        sh1.Cells(I, 2).Resize(sh1.[C3], 4).Copy Sheets(shName).[B5]     '§å¦¸½Æ»s
        I = I + sh1.[C3]
    Loop Until I > Lst1
End Sub

¾Ç¨ì¦Ñ¦º

~¤pªº·PÁ¨â¦ì¤j¤j «üÂI «ùÄòÆp¬ãVBAªººëµØ©Ò¦b ¯à¤O¤WÁÙ¦bªì¾Ç´Á¡A¥[­¿§V¤O¤¤!