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

[µo°Ý] Á`ªí¶×¶°¸Õ°Ý

[µo°Ý] Á`ªí¶×¶°¸Õ°Ý

§Ú²Ä¤@­Ó¬O³o¼Ë¼g¡A¦ý¬O¸õ²Ä¤G­Ó°»´ú³Ì¤U¦Cªº³¡¤À¤@ª½¦³°ÝÃD
    Sheets("¥DÃD1").Select
    ActiveSheet.Range("A2", ActiveSheet.Range("B2").End(xlDown)).Select
    Selection.Copy
    Sheets("Á`ªí").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("a65536").End(xlUp).Select
    Range("A" & ActiveCell.Row + 1).Value = "¡ô¥DÃD1"

    Sheets("¥DÃD2").Select
    ActiveSheet.Range("A2", ActiveSheet.Range("B2").End(xlDown)).Select
    Selection.Copy
    Sheets("Á`ªí").Select
    ....
    ....±q³o¸Ì°»´ú³Ì¤U¦C+1ªº³¡¤À¡A§Ú¦³¤Wºô¬Ý¤å³¹¸Õ¹L«Ü¦h¤èªk¦ý¬O³£¤£¤j¦æ
    ¦A³Â·Ð¦U¦ì¤j¤jÀ°§Ú¬Ý¬Ý ..
    ªþ¤WÀÉ®×Á|¨Ò»¡©ú

book1.zip (370.46 KB)

¦^´_ 1# macro0029
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Sh As Worksheet, Rng As Range, xRng As Range, i As Long
  4.     Set Sh = Sheets("Á`ªí")    '**¸ê®Æ®wªº¤u§@ªí
  5.     Set xRng = Sh.Range("a1", Sh.Range("a1").End(xlToRight).End(xlDown))    '**¸ê®Æ®wªº½d³ò
  6.     Set Rng = Sh.Cells(1, Columns.Count - 1).Resize(2, 2)    '**¶i¶¥¿z¿ïªº·Ç«h½d³ò
  7.     Rng.Range("B1") = Sh.Range("A1").End(xlToRight)    '**¶i¶¥¿z¿ïªº·Ç«hÄæ¦ì [Ãþ§O]
  8.     Rng.Range("A1") = Sh.Range("A1") & "A"   '**¶i¶¥¿z¿ïªº·Ç«hÄæ¦ì [»s²¼¾÷ºcA]
  9.     '*PS:­«­n**  ­pºâ¦¡·Ç«h®É,·Ç«hªºÄæ¦ì¤£¥i»P¸ê®Æ®wªºÄæ¦ì¦P****
  10.     Rng.Range("B2") = "¦¬¤ä½Õ¾ã" '**¶i¶¥¿z¿ïªº·Ç«hÄæ¦ì [Ãþ§O] ±ø¥ó
  11.     With Sheets("¥DÃD1")
  12.         .Cells.Clear
  13.         '***[»s²¼¾÷ºc]¦¹Äæ¸ê®Æªº¼Æ¦r¬°¤å¦r®æ¦¡****
  14.         Rng.Range("A2") = "=»s²¼¾÷ºc<>""7070"""    '**¶i¶¥¿z¿ïªº·Ç«hÄæ¦ì [»s²¼¾÷ºcA] ±ø¥ó
  15.         xRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Rng, CopyToRange:=.[a1]
  16.         '**xRng.AdvancedFilter:¸ê®Æ®wªº¶i¶¥¿z¿ï¤èªk
  17.         '**°Ñ¼Æ xlFilterCopy ¸ê®Æ®wªº¶i¶¥¿z¿ï°Ê§@
  18.         '**°Ñ¼Æ CriteriaRange :·Ç«h½d³ò
  19.         '**°Ñ¼Æ CopyToRange: ¦pªG Action ¬° xlFilterCopy¡A¦¹¤Þ¼Æ§Y¬°½Æ»s¦Cªº¥Øªº½d³ò¡C§_«h¡A·|©¿²¤¦¹¤Þ¼Æ¡C
  20.         .Range("B:C").Delete  '(µo°Ê¾÷ºc,¦C±b¾÷ºc) §R°£
  21.         .Range("E1") = "·|­p¬ì¥Ø"
  22.         i = 2
  23.         Do
  24.         .Cells(i, "E") = .Cells(i, "E") & .Cells(i, "F") '¡]Á`¡A²Ó¡^¡A¦X¨Ö¦ÜEÄæ¡]·|­p¬ì¥Ø¡^
  25.             i = i + 1
  26.         Loop Until .Cells(i, "E") = ""
  27.         .Range("E:E").NumberFormatLocal = "@"  '³]¬°¤å¦r®æ¦¡
  28.         .Range("F:F").Delete
  29.     End With
  30.     With Sheets("¥DÃD2")
  31.         .Cells.Clear
  32.      '   Rng.Range("A2") = "=»s²¼¾÷ºc=""7070"""
  33.         xRng.AdvancedFilter xlFilterCopy, Rng, .[a1]
  34.         .Range("b:c").Delete
  35.         .Range("E1") = "·|­p¬ì¥Ø"
  36.         i = 2
  37.         Do
  38.         .Cells(i, "E") = .Cells(i, "E") & .Cells(i, "F")
  39.             i = i + 1
  40.         Loop Until .Cells(i, "E") = ""
  41.         .Range("E:E").NumberFormatLocal = "@"
  42.         .Range("F:F").Delete
  43.     End With
  44.     Rng.Clear
  45. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 1# macro0029
  1. Sub ex()
  2. Dim ar(), ay()
  3. With Sheets("Á`ªí")
  4. r = 2
  5. Do Until .Cells(r, 1) = ""
  6. If .Cells(r, "J") = "¦¬¤ä½Õ¾ã" Then
  7.    If .Cells(r, "A") = "7070" Then
  8.       ReDim Preserve ar(s)
  9.       ar(s) = Array(.Cells(r, "A"), .Cells(r, "D"), .Cells(r, "E"), .Cells(r, "F"), .Cells(r, "G") & .Cells(r, "H"), .Cells(r, "I"), .Cells(r, "J"))
  10.       s = s + 1
  11.       Else
  12.       ReDim Preserve ay(i)
  13.       ay(i) = Array(.Cells(r, "A"), .Cells(r, "D"), .Cells(r, "E"), .Cells(r, "F"), .Cells(r, "G") & .Cells(r, "H"), .Cells(r, "I"), .Cells(r, "J"))
  14.       i = i + 1
  15.     End If
  16. End If
  17. r = r + 1
  18. Loop
  19. End With
  20. Sheets("»s²¼¾÷ºc¬O7070¥BÃþ§O¬°¦¬¤ä½Õ¾ã").[A2].Resize(s, 7) = Application.Transpose(Application.Transpose(ar))
  21. Sheets("»s²¼¾÷ºc«D7070¥BÃþ§O¬°¦¬¤ä½Õ¾ã").[A2].Resize(i, 7) = Application.Transpose(Application.Transpose(ay))
  22. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD