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

[µo°Ý] ¦Û°Ê²M°£¥\¯à

[µo°Ý] ¦Û°Ê²M°£¥\¯à

¥»©«³Ì«á¥Ñ PJChen ©ó 2017-7-25 22:36 ½s¿è

½ÐÀ°À°¦£,³o­Óµ{¦¡¤w¶i¤J³Ì«á¶¥¬q´N§¹¦¨¤F...

¨Ó·½Àɦ³1064¦C
¥ØªºÀɦ³1073¦C
·í¨Ó·½Àɧ⦳¸ê®Æªº½d³òA:AA½Æ»s¨ì¥ØªºÀÉB:AB«á,¦Û°Ê²M°£¥ØªºÀÉ1065:1073¦Cªº¦r(­n¯àDelete¤@¾ã¦C¦ý¤£­n§R°£¦C,¦]¬°¥ØªºÀɪºACÄ椧«áÁÙ¦³¤½¦¡)

ª`·N¨Æ¶µ¡G
1. ³o­Óµ{¦¡´ú¸Õ¹L¨S°ÝÃD,¥u¬O·í¥ØªºÀɸê®Æ¦h©ó¨Ó·½ÀÉ®É,§Ú§Æ±æ¼W¥[¤@¶µ²M°£"¦h¾l¸ê®Æ"ªº¥\¯à
2. ²M°£¦h¾l¸ê®Æªº¼gªk,­n¯à¦Û°Ê°»´ú,¦]¬°¨Ó·½ÀɤΥتºÀɪº¸ê®ÆÀH®É·|¦³ÅÜ°Ê¡A©Ò¥H¤£¯à¥ÎDelete ²Ä?¦C:²Ä?¦Cªº¼gªk
3. ¥ØªºÀɤ¤ªº1102~1104¦C¦³­pºâ¤½¦¡¡A§Ú¤j¬ù³£·|Åý¥¦»P¸ê®Æ«O«ù10¦C¥H¤Wªº¶ZÂ÷,©Ò¥H½Ð§â³o­Ó¤]¦Ò¶q¶i¥h,¥¦¤£¯à³QDelete
4. ½Ð¾¨¥i¯à¤£­n­×§ï­ì¥ýªºµ{¦¡

20170725.220357.jpg
2017-7-25 22:23
  1. Sub ®w¦s§ó·s()
  2. '
  3. '
  4. '

  5. '
  6.     Dim Msg As Boolean, W As Workbook, Wb As Workbook  'W As "¨Ó·½ÀÉ"      Wb As "¥ØªºÀÉ"
  7.    
  8.     'Boolean «¬ºAªº¹w³]­È¬° False
  9.     '*******Workbooks ¶}±Òªº¬¡­¶Ã¯ª«¥ó¶°¦X****
  10.     For Each W In Workbooks
  11.         If UCase(W.Name) = UCase("®w¦s¸ê®Æªí.xlsx") Then  'UCaseªº¥\¯à¬O¤°»ò?
  12.             Msg = True  'Àɮפw¶}±Ò
  13.             Exit For
  14.         End If
  15.     Next
  16.     '*****************************************
  17.     If Msg = True Then 'Àɮפw¶}±Ò
  18.         Set W = Workbooks("®w¦s¸ê®Æªí.xlsx")
  19.     Else               'Àɮש|¥¼¥´¶}®É
  20.         Set W = Workbooks.Open("Q:\00_¬ì¼Ý\¥X³f¤å¥ó³sµ²\FromERP\®w¦s¸ê®Æªí.xlsx")
  21.     End If

  22.     '*****************************************
  23.     If Msg = True Then 'Àɮפw¶}±Ò
  24.         Set Wb = Workbooks("ERP_Data.xlsx")
  25.     Else               'Àɮש|¥¼¥´¶}®É
  26.         Set Wb = Workbooks.Open("Q:\00_¬ì¼Ý\¥X³f¤å¥ó³sµ²\ERP_Data.xlsx")
  27.     End If

  28.     '*****************************************Sorting¥H.Range("L1")¬°­º
  29. Windows("®w¦s¸ê®Æªí.xlsx").Activate
  30.     Range("G1").Select
  31.     Selection.AutoFilter  '«Ø¥ß¦Û°Ê¿z¿ï
  32.     Range("G2").Select
  33. ActiveWindow.FreezePanes = True '­áµ²window
  34. 'ActiveWindow.FreezePanes = False '¨ú®ø­áµ²window
  35. With Workbooks("®w¦s¸ê®Æªí.xlsx")
  36.    With .Sheets("®w¦s¸ê®Æªí")
  37.    Set b = .Range("L1").CurrentRegion
  38. A = Array("L", "F") '­Y¦³¨ä¥LSorting¶¶¦ì¤]¥i¥[¤J
  39.     .AutoFilter.Sort.SortFields.Clear
  40.     For i = 0 To 1   '«üA = Array("L", "F") ¦³´X­Ósorting¶µ¥Ø,2­Ó´N¬O0 To 1
  41.        .AutoFilter.Sort.SortFields.Add Key:=b.Columns(A(i)) _
  42.         , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  43.         xlSortNormal
  44.     Next
  45.     With .AutoFilter.Sort
  46.         .Header = xlYes
  47.         .MatchCase = False
  48.         .Orientation = xlTopToBottom
  49.         .SortMethod = xlPinYin
  50.         .Apply
  51.     End With
  52.   End With
  53. End With
  54. W.Save
  55.         'Workbooks("®w¦s¸ê®Æªí.xlsx").Close True    '¦sÀÉ«áÃö³¬ÀÉ®×
  56.         
  57.     '*****************************************
  58.     With W.Sheets("®w¦s¸ê®Æªí")
  59.         Set A = Intersect(.UsedRange, .Range("A:AA")).SpecialCells(xlCellTypeVisible)  '¥u¿ï¾Ü¦³¸ê®Æªº½d³ò
  60.      End With
  61.         
  62.      With Wb
  63.         'a.Copy .Sheets("®w¦s").Range("B1")  '§¹¥þ½Æ»s¨ìsheetªºB1
  64.         '*************************************
  65.         A.Copy
  66.         .Sheets("®w¦s").Range("B1").PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È
  67.         '*************************************
  68.         Application.CutCopyMode = False      '***¤£³B©ó°Å¤U©Î½Æ»s¼Ò¦¡
  69.         '.Close True    '¥ØªºÀɦsÀÉ«áÃö³¬ÀÉ®×
  70.    Wb.Save   '¥ØªºÀɦsÀÉ
  71.     End With
  72.     W.Close False '¨Ó·½ÀÉÃö³¬ÀÉ®×(¤£·|°Ý¬O§_¦sÀÉ)
  73. End Sub
½Æ»s¥N½X

¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-7-29 19:19 ½s¿è

¦^´_ 1# PJChen

UCaseªº¥\¯à¬O¤°»ò?
§A¾Ç²ßªº¤£°÷»{¯u,¤£À´­n¬Ý¬Ý»¡©ú

¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Dim ¥ØªºÀÉ As Workbook, ¨Ó·½ÀÉ As Workbook
  3. Sub ®w¦s§ó·s()
  4.     Dim xRng As Range
  5.     File_settings ¨Ó·½ÀÉ, "®w¦s¸ê®Æªí.xlsx"
  6.     File_settings ¥ØªºÀÉ, "ERP_Data.xlsx"
  7.     With ¨Ó·½ÀÉ.Sheets(1)
  8.         .Cells.Sort Key1:=.Columns("L"), Key2:=.Columns("F"), Header:=xlYes
  9.         Set xRng = .UsedRange  'UsedRange->¤u§@ªí©Ò¨Ï¥Îªº½d³ò,¦pA:AA¤§«á¦A¤]¨S¸ê®Æ
  10.         xRng.Copy 'A:AA½Æ»s¨ì¥ØªºÀÉB:AB
  11.     End With
  12.     With ¥ØªºÀÉ.Sheets("®w¦s")
  13.        .Range("B1").PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È
  14.         '¦Û°Ê²M°£¥ØªºÀÉ1065:1073¦C
  15.         .Range("a" & xRng.Rows.Count + 1, .Range("A1101")).Resize(, 27).Clear
  16.     End With
  17.     ¨Ó·½ÀÉ.Close False
  18.     ¥ØªºÀÉ.Save
  19. End Sub
  20. Sub File_settings(xFile As Workbook, ¤u§@­¶ As String) 'Àɮ׳]©w
  21.     Dim xPath As String
  22.     xPath = ThisWorkbook.Path & "\"
  23.     If UCase(¤u§@­¶) <> UCase("ERP_Data.XLSX") Then xPath = xPath & "FromERP\"
  24.     On Error Resume Next
  25.     Set xFile = Workbooks(¤u§@­¶)
  26.     If Err > 0 Then Set xFile = Workbooks.Open(xPath & ¤u§@­¶)
  27.     If xFile.Name = "" Then
  28.         MsgBox "½Ð¬d¬Ý " & vbLf & xPath & vbLf & "¬O§_¦³ [" & ¤u§@­¶ & "]"
  29.         End
  30.     End If
  31. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2017/7/28 10:49): 2# ¤w«ü¦W±Æ§Çµ{¦¡½X¥i¼g¦b¨º¸Ì

¦^´_ 2# GBKEE

¤j¤j,

¦]¬°§Ú­ì¨Óªºµ{¦¡¦³±Æ§Ç¥\¯à¡A³o­Ó·sªºµ{¦¡¨S§â¨Ó·½Àɪº±Æ§Ç¥\¯à¼g¶i¥h,
1. ¥i¥HÀ°¦£§â¥¦¥[¤W¥h¶Ü¡H
2. §ó·sµ{¦¡«á,­n§â¨Ó·½ÀÉÃö³¬.

§Ú¥»¨Ó·Q­ì¨Óªº±Æ§Ç¥\¯àª½±µ²K¤W¥h¡A¦ý¦]¬°§Aªº¼gªk¤£¦P,§Ú¥[¤W¥hªº¸Ü¥¦´N¾ã­Ó¤£¯à°õ¦æ¤F¡C

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-7-29 18:15 ½s¿è

¦^´_ 2# GBKEE

¤j¤j,
½Ð«ü¾É¤@¤U,§Ú¬O­n¨Ó·½Àɦb¥¼½Æ»s¸ê®Æ¤§«e¥ý±Æ§Ç,¦ýÁ`¬O¦³¨Ç°ÝÃD
1) ¨Ó·½ÀÉ»P¥ØªºÀɦP®É¨Æ¥ý¶}±Ò,©Î¦P®É¤£­n¶}±Ò,¥¦³£µLªk°õ¦æ
2) ¥ý¶}±Ò¥ØªºÀÉ,¦A°õ¦æ¥¨¶°³o®É¥¦·|¦Û¦æ¥´¶}¨Ó·½ÀÉ,³o¼Ë´N¥i¥H§¹¥þ°õ¦æ¤]·|¥ý±Æ§Ç¦A¶K¤W.
3) §Ú­n«ç»ò­×§ïÅý¥¦§¹¥þ¥¿±`¡H
  1. Option Explicit
  2. Dim ¥ØªºÀÉ As Workbook, ¨Ó·½ÀÉ As Workbook
  3. Sub ®w¦s§ó·s()
  4.     Dim xRng As Range
  5.     File_settings ¨Ó·½ÀÉ, "®w¦s¸ê®Æªí.xlsx"
  6.     File_settings ¥ØªºÀÉ, "ERP_Data.xlsx"
  7.     Set xRng = ¨Ó·½ÀÉ.Sheets(1).UsedRange  'UsedRange->¤u§@ªí©Ò¨Ï¥Îªº½d³ò,¦pA:AA¤§«á¦A¤]¨S¸ê®Æ
  8.    
  9.     '¨Ó·½ÀɲĤ@­Ó¤u§@ªí,±Æ§Ç 1) L  2)F
  10.      With ¨Ó·½ÀÉ.Sheets(1).Range("A:AA")
  11.     .Cells.Sort Key1:=.Columns("L"), Key2:=.Columns("F"), _
  12. Header:=xlYes
  13.          xRng.Copy 'A:AA½Æ»s¨ì¥ØªºÀÉB:AB
  14.     With ¥ØªºÀÉ.Sheets("®w¦s")
  15.        .Range("B1").PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È
  16.         
  17.         '¦Û°Ê²M°£¥ØªºÀÉ1065:1073¦C
  18.         .Range("a" & xRng.Rows.Count + 1, .Range("A1101")).Resize(, 37).Clear
  19.         End With
  20.     End With
  21.     ¨Ó·½ÀÉ.Close False '¨Ó·½ÀÉÃö³¬ÀÉ®×(¤£·|°Ý¬O§_¦sÀÉ)
  22.     ¥ØªºÀÉ.Save
  23.    
  24.     End Sub
  25. Sub File_settings(xFile As Workbook, ¤u§@­¶ As String) 'Àɮ׳]©w
  26.     Dim xPath As String
  27.     xPath = ThisWorkbook.Path & "\"
  28.     If UCase(¤u§@­¶) <> UCase("ERP_Data.XLSX") Then xPath = xPath & "FromERP\"
  29.     On Error Resume Next
  30.     Set xFile = Workbooks(¤u§@­¶)
  31.     If Err > 0 Then Set xFile = Workbooks.Open(xPath & ¤u§@­¶)
  32.     If xFile.Name = "" Then
  33.         MsgBox "½Ð¬d¬Ý " & vbLf & xPath & vbLf & "¬O§_¦³ [" & ¤u§@­¶ & "]"
  34.         End
  35.     End If
  36. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# PJChen

1) ¨Ó·½ÀÉ»P¥ØªºÀɦP®É¨Æ¥ý¶}±Ò,©Î¦P®É¤£­n¶}±Ò,¥¦³£µLªk°õ¦æ??
2) ¥ý¶}±Ò¥ØªºÀÉ,¦A°õ¦æ¥¨¶°³o®É¥¦·|¦Û¦æ¥´¶}¨Ó·½ÀÉ,³o¼Ë´N¥i¥H§¹¥þ°õ¦æ¤]·|¥ý±Æ§Ç¦A¶K¤W.    ??

Sub ®w¦s§ó·s(), ³oµ{§Ç§A¤£¬O­n©ñ¦bVBA³øªí«ü¥O.XLSM
µ{¦¡½X ¦³«ü©w  ¨Ó·½ÀÉ»P¥ØªºÀÉ, ¥i¥H¤£ºÞ¨Æ¥ý¶}±Ò,©Î¨S¶}±Òªº°ÝÃD
  1. File_settings ¨Ó·½ÀÉ, "®w¦s¸ê®Æªí.xlsx"
  2.     File_settings ¥ØªºÀÉ, "ERP_Data.xlsx"
  3. '**********¨Ó·½ÀɬO¦PVBA³øªí«ü¥Oªº¸ê®Æ§¨\FromERP\*********
  4. If UCase(¤u§@­¶) <> UCase("ERP_Data.XLSX") Then xPath = xPath & "FromERP\"
½Æ»s¥N½X
2#µ{¦¡½X¤w§ó·s ¦A¸Õ¸Õ¬Ý
§A4#ªºµ{¦¡½X°õ¨S¿ù£«
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE

¤j¤j,

§Ú¤S¸Õ¤F¦n´X¹M,ÁÙ¬O¸ò¤§«e»¡ªº¤@¼Ë,¥²¶·¥ý¶}±ÒERP_Data.xlsx,°õ¦æ¤~·|¥¿±`..
§ÚPO¤WÅý±z°õ¦æ¬Ý¬Ý.
Try_20170729.rar (709.34 KB)

TOP

¦^´_ 6# PJChen
©êºp¤F,2­Ó¦a¤è¤Ö¥[ " ."
¦A¸Õ¸Õ¬Ý

With ¨Ó·½ÀÉ.Sheets(1)
        .Cells.Sort Key1:= .Columns("L"), Key2:= .Columns("F"), Header:=xlYes
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE

¯u¤Ó¯«©_¤F,..¤j¤j
²{¦b¥¿±`¤F¡A¬°¤°»ò¤Ö¤F"."®t³o»ò¦h¡H...ÁÂÁ§A¤F,¯u¬O¤Ó­W´o¤H¤F.

TOP

¦^´_ 8# PJChen
With ¨Ó·½ÀÉ.Sheets(1)    '³oª«¥ó¬O¤u§@ªí
        .Cells.Sort Key1:=Columns("L"), Key2:=.Columns("F"), Header:=xlYes
        '** ¨S¦³¤@ÂIªº   Columns("L"), ¬O«ü§@¥Î¤¤¤u§@ªíªºColumns("L")
         '** ¦p«ê¥©§@¥Î¤¤¤u§@ªí¬O,¨Ó·½ÀÉ.Sheets(1),,¤£·|¦³¿ù»~µo¥Íªº
       '**³o¬O¤p¦a¤è«Ü®e©ö²¨©¿ªº
        Set xRng = .UsedRange    'UsedRange->¤u§@ªí©Ò¨Ï¥Îªº½d³ò,¦pA:AA¤§«á¦A¤]¨S¸ê®Æ
        xRng.Copy 'A:AA½Æ»s¨ì¥ØªºÀÉB:AB
    End With
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 9# GBKEE

·PÁ¤j¤j»¡©ú...

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD