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

[µo°Ý] µ{¦¡µLªk²M°£¸ê®Æ,¦Ó¥B°õ¦æ³t«×«ÜºC

[µo°Ý] µ{¦¡µLªk²M°£¸ê®Æ,¦Ó¥B°õ¦æ³t«×«ÜºC

½Ð°Ý¤G­Ó°ÝÃD:       ¤â°ÊLabel.rar (270.11 KB)

1. ³o¬qµ{¦¡¬O¥ý±NSheets("Label (3)")ªºJ2~J endªº¸ê®Æ²M°£¡A¦ý³o­Ó«o¼gªkµLªk²M°£¸ê®Æ.
  1.    Set Wb = Workbooks("³Ì·s®w¦s.xlsx")
  2.     Set S = Wb.Sheets("Label (3)")
  3.     S.Select
  4.     With S
  5.     Set A = .Range("J2", .Range("J2").End(xlDown))  '±q«ü©wÂI¨ì¸ê®Æ³Ì©³³¡....¦ý³o­Ó¼gªk¤£¯à¦Û°Ê²M°£
  6.     A.ClearContents  '²M°£¸ê®Æ
  7.     End With
½Æ»s¥N½X
2.  Sheets("Label (3)")¤w¤â°Ê§â»Ý­n¥´¤Äªº³¡¥÷¤â°Ê¸É¤W¡A­ì¥»ªºªº¸ê®Æ¦³¦n´X¤d¦C¡A³o­Ó¼gªk¦b°õ¦æ®É·|°±¹y«Ü¤[¡A¹³µ{¦¡§Ö·í±¼ªº·Pı¡A½Ð°Ý¬O§_¦³¤£¦P¼gªk¡A¥i¥HÅý¥¦°õ¦æ§Ö¤@ÂI¡H
³o­Óµ{¦¡ªº°õ¦æ¹Lµ{¬OÅýJÄ楴¤Äªº³¡¥÷¡A¥þ³¡·s¼W¤G¦C¡AµM«á±N­ì¥»¥´¤Äªº¦P¦CCÄæ¸ê®Æ,½Æ¨î¶K¤W¨ì·s¼W¦CªºBÄ椤¡A¦pªGµLªk©ú¥Õ§Úªº¸Ñ»¡¡A½Ð±Nªþ¥óªºÀÉ®×°õ¦æ¬Ý¬Ý¡A¥i¯à¤ñ¸û®e©öÀ´¡A¤£¹L¦]¬°¸ê®Æ¤w¸g´î¤Ö¤F¡A¥i¯à¤ñ¸û¤£·Pı"¥d¥d"ªº¡A¤£¹L¯u¹ê±¡ªp¬O¶]±o«Ü¤£²z·Q.
  1.    Set Wb = Workbooks("³Ì·s®w¦s.xlsx")
  2.     Wb.Activate
  3.    
  4.     Set S = Wb.Sheets("Label (3)")
  5.    With S
  6.     .UsedRange.AutoFilter Field:=10, Criteria1:="", Operator:=xlAnd  'ªÅ®æªº¸ê®Æ§R°£
  7.     Set xRng = .UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible)  '¿ï¾Ü¦³¸ê®Æªº½d³ò§R°£,Offset(1, 0)ªí¥ÜªíÀY©¹¤U¤@¦C¶}©l
  8.     xRng.Delete
  9.     .UsedRange.AutoFilter Field:=10 '¨ú®ø¿z¿ï
  10.     End With

  11.    With S
  12.         LastRow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
  13.         For J = 1 To LastRow
  14.             If Range("J" & J).Value = "V" Then
  15.                 LastRow = LastRow + 2
  16.             End If
  17.         Next J
  18.    
  19.         For J = 1 To LastRow
  20.         If Range("J" & J).Value = "V" Then  'JÄæ"V",«h·s¼W2¦C
  21.         Rows(J + 1).Insert Shift:=xlDown
  22.         Rows(J + 1).Insert Shift:=xlDown
  23.         Range("C" & J).Copy
  24.         Range("B" & J + 1).Select   '"V"¦P¦CªºCÄæ­Ècopy¨ì·s¼W²Ä1¦CªºBÄæ
  25.         ActiveSheet.Paste
  26.         End If
  27.     Next J
  28. End With
  29.    
  30.         ActiveWorkbook.SaveAs "D:\5_VBA¿é¥X³øªí\®w¦s_" & Format(Date, "YYYYMMDD") & "." & Format(Time, "HHMM") & ".xlsx"
  31.     Kill "D:\Download\¼È¦s\³Ì·s®w¦s.xlsx"
  32.     End Sub
½Æ»s¥N½X

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD