GBKEE

¦^´_ 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

GBKEE

¦^´_ 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¿ù£«

GBKEE

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

With ¨Ó·½ÀÉ.Sheets(1)
        .Cells.Sort Key1:= .Columns("L"), Key2:= .Columns("F"), Header:=xlYes

GBKEE

¦^´_ 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