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

±N¸ê®Æ½Æ»s¨ì¤£¦Pªº¤u§@ªí

±N¸ê®Æ½Æ»s¨ì¤£¦Pªº¤u§@ªí

ªþ¥ósheet1¬O©Ò¦³ªº¸ê®Æ¤u§@ªí,
·í¸ê®Æ¦³²§°Ê®É,±N¨ä¥L¤u§@ªíªº¸ê®Æ²MªÅ (¸ê®Æ©ïÀY¤£²M°£), ¦A±Nsheet1ªº¸ê®Æ¨Ì¤£¦PªºSales½Æ»s¨ì¬Û¹ïÀ³ªº¤u§@ªí¸Ì.
½Ð±ÐVBAµ{¦¡¦p¦ó¼g?
·PÁÂ...

ABC.rar (4.24 KB)

fangac

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2012-12-10 20:20 ½s¿è

¦^´_ 1# fangsc
°Ñ¦Ò¬Ý¬Ý¤U­±µ{¦¡ , ¦ý¨ä¹ê¸ê®Æ¶q­Y¬O«Ü¤jªº¸Ü¤£¬O«Ü«Øijª½±µ¦bWorksheet_Change¨Æ¥ó¤º¥h³B²z
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim ws As Worksheet
  4.     Dim r As Long
  5.     Dim sales As String
  6.                
  7.     Application.ScreenUpdating = False
  8.    
  9.     'clear header
  10.     For Each ws In Sheets
  11.         If not ws is Me Then
  12.             ws.UsedRange.Offset(1).ClearContents
  13.         End If
  14.     Next
  15.    
  16.     For r = 2 To Range("C" & Rows.Count).End(xlUp).Row
  17.         sales = Cells(r, "C").Value
  18.         
  19.         If sales <> "" Then
  20.             If Not hasSheet(sales) Then
  21.                 With Sheets.Add(After:=Sheets(Sheets.Count))
  22.                     .name = sales
  23.                     [A1:H1].Copy .[A1]
  24.                 End With
  25.             End If
  26.             
  27.             With Sheets(sales)
  28.                 .Range("C" & .Rows.Count).End(xlUp).Offset(1, -2).Resize(1, 8) = Range("A" & r & ":" & "H" & r).Value
  29.             End With
  30.         End If
  31.     Next r
  32.    
  33.     Me.Select
  34.     Application.ScreenUpdating = True
  35. End Sub

  36. Function hasSheet(name As String) As Boolean
  37.     Dim ws As Worksheet
  38.    
  39.     On Error Resume Next
  40.     Set ws = Sheets(name)
  41.     On Error GoTo 0
  42.    
  43.     If ws Is Nothing Then
  44.         hasSheet = False
  45.     Else
  46.         hasSheet = True
  47.     End If
  48.    
  49.     Set ws = Nothing
  50. End Function
½Æ»s¥N½X

TOP

¦^´_ 1# fangsc
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. With Sheet1
  4.   For Each a In .Range(.[C2], .[C2].End(xlDown))
  5.     If d.exists(a.Value) = False Then
  6.        d(a.Value) = a.Address
  7.        Else
  8.        d(a.Value) = d(a.Value) & "," & a.Address
  9.     End If
  10.   Next
  11. For Each ky In d.keys
  12.    Sheets(ky).UsedRange.Offset(1).ClearContents
  13.   .Range(d(ky)).EntireRow.Copy Sheets(ky).[A2]
  14. Next
  15. End With
  16. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-12-11 08:33 ½s¿è

¦^´_ 1# fangsc
¥i¥Î¤u§@ªí¦Û°Ê¿z¿ï
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Sh As Worksheet
  4.     'Dim «Å§iÅܼÆ
  5.     For Each Sh In Sheets
  6.     'For °j°é: ¤u§@ªíª«¥ó¶°¦X(¦¹¬¡­¶Ã¯)¤¤, ¨Ì§Ç³B¸Ì¤u§@ªí
  7.         If Sh.Name <> "Sheet1" Then
  8.         '¬¡­¶Ã¯¤u§@ªíªº¦WºÙ ¤£¬O­n½Æ»s¸ê®Æªº¤u§@ªí
  9.             With Sheets("Sheet1")
  10.             '¦b­n½Æ»s¸ê®Æªº¤u§@ªí(ª«¥ó¤¤)
  11.                 .AutoFilterMode = False
  12.                 '¨ú®ø¦¹¤u§@ªí¦Û°Ê¿z¿ï
  13.                 .Range("A1").AutoFilter 3, Sh.Name
  14.                 '¤u§@ªíA1ªº³sÄò½d³ò¬°¦Û°Ê¿z¿ïªº½d³ò,³sÄò½d³òªº²Ä3Ä欰·Ç«h:= Sh¤u§@ªíªº¦WºÙ
  15.               .UsedRange.Copy Sh.[A1]
  16.                 '½Æ»sSheets("Sheet1") ¨ìSh¤u§@ªí
  17.             End With
  18.             'µ²§ô(ª«¥ó¤¤)
  19.         End If
  20.         'µ²§ô(If Sh.Name <> "Sheet1" Then)
  21.     Next
  22.     '¦^¨ì¹ïÀ³ªº For °j°é ª½¨ì³B¸Ì§¹²¦
  23.     Sheets("Sheet1").AutoFilterMode = False
  24.     'Sheets("Sheet1")¤u§@ªí¨ú®ø¦Û°Ê¿z¿ï
  25. End Sub
½Æ»s¥N½X

TOP

Hsieh ª©¤jªº¸ê®Æ¦r¨å ("Scripting.Dictionary")¡B
¥H¤Î GBKEE  ª©¤jªº¤u§@ªí¦Û°Ê¿z¿ï (AutoFilter)¡A
¹ï©ó"³B²z±N¸ê®Æ½Æ»s¨ì¤£¦Pªº¤u§@ªí"ªº³B²z¹Lµ{
³£¦U¦³¨ä¯S¦â¡A¬O­ÓÆZ­È±o°Ñ¦Òªº¸ê®Æ¡AÁÂÁ¡I

TOP

³o¦U¦Û°Ê¿z¿ïªº¤è¦¡®¼Â²¼ä
¤S¨£ÃѨ줣¦P¤è¦¡
¦r¨å¤]¬O¦n¤è¦¡
ÁÙ¦b¾Ç²ß¤¤
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 4# GBKEE

·PÁÂStillfish00, ¶W¯Åª©¥D¤ÎGBKEEª©¥D,¦]¬°³Ìªñ¤u§@¹ê¦b¤Ó¦£,¨S¦³®É¶¡¶i¨Ó¬Ý¦U¦ìªº«ü¾É.
À³¸Ó¤]¬O¨S¦³§âExcel¨ç¼Æ¤ÎVBA¾Çºë,¤u§@¤W¤~µLªk°µ¨ì¨Æ¥b¥\­¿§a!
µ{¦¡¦¬¤U¤F,ºÉ¤O¬ã¨s¤@¤U. ·P®¦!!
fangac

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD