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

[µo°Ý] 1±i¥D¤u§@ªí¿z¿ï«á·s¼W¤u§@ªí¤Î©R¦W

[µo°Ý] 1±i¥D¤u§@ªí¿z¿ï«á·s¼W¤u§@ªí¤Î©R¦W

sub copyfile()
'±NDATA¤u§@ªí¤º«ö³¡ªù¿z¿ï«á¥t¦s¤u§@ªí
  1. For i = 1 To 3
  2. Cells(1, 1).AutoFilter 3, i
  3. Cells(1, 1).CurrentRegion.Copy '³o¼Ë¥X¿ù, ¥u¥i±N³¡¥÷¤º®e¥t¦s¤u§@ªí
  4. Worksheets.Add after:=Worksheets(Worksheets.Count) 1¤u§@ªí¿z¿ï«á¥t¦s¤u§@ªí¤Î¥H¿z¿ïµ²ªG©R¦W.zip (28.96 KB)
  5. Worksheets(Worksheets.Count).Name = "SHEET" & i
  6. ActiveSheet.Paste
  7. Application.CutCopyMode = False
  8. Sheets(1).Select
  9. ActiveSheet.ShowAllData
  10. Next i
  11. ActiveSheet.AutoFilterMode = False
  12. End Sub


  13. Sub myName() '¥Î¨C¤u¤´ªíªºC6Àx¦s®æ¬°¤u§@ªí©R¦W
  14. For i = 2 To Worksheets.Count
  15. Worksheets(i).Name = Worksheets(i).Cells(2, 1)
  16. Next
  17. End Sub
½Æ»s¥N½X

  1. Sub zz()
  2. Application.ScreenUpdating = 0
  3. Dim rng As Range, d As Object, ws As Worksheet
  4. Set ws = ActiveSheet
  5. ws.AutoFilterMode = False
  6. c = [a3].CurrentRegion.Columns.Count
  7. Set d = CreateObject("scripting.dictionary")
  8. Set rng = Range([a5], Cells(Cells(Rows.Count, 1).End(3).Row, c))
  9. a = rng.Columns(3)
  10. For i = 2 To UBound(a)
  11.     d(a(i, 1)) = ""
  12. Next
  13. k = d.keys
  14. For i = 0 To UBound(k)
  15.     ws.Copy after:=Sheets(Sheets.Count)
  16.     ActiveSheet.AutoFilterMode = False
  17.     [a3].CurrentRegion.Clear
  18.     With ws
  19.         rng.AutoFilter Field:=3, Criteria1:=k(i)
  20.         .[a3].CurrentRegion.Copy [a3]
  21.         
  22.     End With
  23.     ActiveSheet.Name = [c6].Value
  24. Next
  25. Application.ScreenUpdating = 1
  26. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# ikboy
·PÁÂ, §Ú­n¬¨¤Æ¤Î¾Ç²ß.:D

TOP

¥»©«³Ì«á¥Ñ missbb ©ó 2018-6-11 19:32 ½s¿è

¤j¤jIKBOY§A¦n, §Ú±N§A«ü¾Éªº©ñ¤J¸û¦h¸ê®ÆªºEXCEL, ¦Û¤v¥[¤JÀ˵ø¸ê®Æªº§ï°Ê.  ¦ý°õ¦æ®É¥X²{1004ªº¿ù»~, ½ÐÀ°¦£¬Ý¨º¸Ì¥X¿ù?

§Ú¬O­n¦bSHEET DATA¤§«á, «O¯d¤u§@ªí1¤Î¤u§@ªí¤G,§Y¤À³¡ªù·s¼Wªº¤u§@ªí, ¬O­n¦b¤u§@ªí¤G¤§«á·s¼W.

new.zip (37.07 KB)
  1. Sub sortingsavesheet2()
  2. 'use this , sorting dept then save individualsheet with deptname

  3. Application.ScreenUpdating = 0
  4. Dim rng As Range, d As Object, ws As Worksheet
  5. Set ws = ActiveSheet
  6. ws.AutoFilterMode = False
  7. c = [a3].CurrentRegion.Columns.Count
  8. Set d = CreateObject("scripting.dictionary")
  9. Set rng = Range([a5], Cells(Cells(Rows.Count, 1).End(3).Row, c))
  10. a = rng.Columns(3)
  11. For i = 2 To UBound(a)
  12.     d(a(i, 1)) = ""
  13. Next
  14. k = d.keys
  15. For i = 0 To UBound(k)
  16.     ws.Copy after:=Sheets(Sheets.Count)
  17.     ActiveSheet.AutoFilterMode = False
  18.     [a3].CurrentRegion.Clear
  19.     With ws
  20.         rng.AutoFilter Field:=3, Criteria1:=k(i)
  21.         .[a3].CurrentRegion.Copy [a3]
  22.         
  23.     Range("F6").Select
  24.     With ActiveWindow
  25.         .SplitColumn = 5
  26.         .SplitRow = 5
  27.     End With
  28.     ActiveWindow.FreezePanes = True
  29.       
  30.     End With
  31.       
  32.     ActiveSheet.Name = [c6].Value
  33. Next
  34. Application.ScreenUpdating = 1

  35. With Sheets("data").Activate
  36.     If ActiveSheet.FilterMode Then
  37.       ActiveSheet.ShowAllData
  38.     End If
  39. [color=Red]'¦Û¦æ¥[¤J­áµ²µ¡¤Î³Ì«áªð¦^DATAªºF6[/color]  
  40. Range("F6").Select
  41. With ActiveWindow
  42.         .SplitColumn = 5
  43.         .SplitRow = 5
  44.     End With
  45.     ActiveWindow.FreezePanes = True
  46.    
  47. End With
  48. End Sub
½Æ»s¥N½X
¦^´_ 2# ikboy

TOP

ÀÉ®×µLªk¥´¶}

TOP

½Æ¥» new.zip (30.05 KB)
¸É¦^ªþ¥ó:P
¦^´_ 5# ikboy

TOP

¥N½X¤£­n©ñ¤Jsheets¤º¡C

½Æ¥» new.zip (35.12 KB)

TOP

·PÁ¨ó¤O

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD