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

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

¦^´_ 9# GBKEE

¤j¤j,
½Ð±Ðsortingªº°ÝÃD,³o­Óµ{¦¡­ì¥»¬O¦b¨Ó·½ÀÉsorting,²{¦b§Ú§â¥¦§ï¨ì¥ØªºÀÉ,°õ¦æ¶Kªº°Ê§@«á¦Asorting,¬°¦ó´N¤£¯à°õ¦æsorting°Ê§@©O¡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. '*********************************************************
  8.     With ¨Ó·½ÀÉ.Sheets(1)
  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.         .Range("a" & xRng.Rows.Count + 1, .Range("A1101")).Resize(, 37).Clear
  15.         .Cells.Sort Key1:=.Columns("AD"), Key2:=.Columns("G"), Header:=xlYes
  16.     End With
  17.     ¨Ó·½ÀÉ.Close False
  18.     ¥ØªºÀÉ.Save
  19. End Sub
  20. '**********¨Ó·½ÀɬO¦PVBA³øªí«ü¥Oªº¸ê®Æ§¨\FromERP\*********
  21. Sub File_settings(xFile As Workbook, ¤u§@­¶ As String) 'Àɮ׳]©w
  22.     Dim xPath As String
  23.     xPath = ThisWorkbook.Path & "\"
  24.     If UCase(¤u§@­¶) <> UCase("ERP_Data.XLSX") Then xPath = xPath & "FromERP\"
  25.     On Error Resume Next
  26.     Set xFile = Workbooks(¤u§@­¶)
  27.     If Err > 0 Then Set xFile = Workbooks.Open(xPath & ¤u§@­¶)
  28.     If xFile.Name = "" Then
  29.         MsgBox "½Ð¬d¬Ý " & vbLf & xPath & vbLf & "¬O§_¦³ [" & ¤u§@­¶ & "]"
  30.         End
  31.     End If
  32. End Sub
½Æ»s¥N½X

TOP

½Ð°ª¤H«üÂI¡G

±¡§Î¤@,³æ¿W±Æ§Ç®É¨S°ÝÃD
  1. Option Explicit
  2. Dim ¥ØªºÀÉ As Workbook, ¨Ó·½ÀÉ As Workbook
  3. Sub sorting()
  4.     Dim xRng As Range
  5.      File_settings ¥ØªºÀÉ, "ERP_Data.xlsx"
  6.     With ¥ØªºÀÉ.Sheets("®w¦s")
  7.         .Cells.Sort Key1:=.Columns("AD"), Key2:=.Columns("G"), Header:=xlYes
  8.     End With
  9.     ¥ØªºÀÉ.Save
  10. End Sub

  11. '**********¨Ó·½ÀɬO¦PVBA³øªí«ü¥Oªº¸ê®Æ§¨\FromERP\*********
  12. Sub File_settings(xFile As Workbook, ¤u§@­¶ As String) 'Àɮ׳]©w
  13.     Dim xPath As String
  14.     xPath = ThisWorkbook.Path & "\"
  15.     If UCase(¤u§@­¶) <> UCase("ERP_Data.XLSX") Then xPath = xPath & "FromERP\"
  16.     On Error Resume Next
  17.     Set xFile = Workbooks(¤u§@­¶)
  18.     If Err > 0 Then Set xFile = Workbooks.Open(xPath & ¤u§@­¶)
  19.     If xFile.Name = "" Then
  20.         MsgBox "½Ð¬d¬Ý " & vbLf & xPath & vbLf & "¬O§_¦³ [" & ¤u§@­¶ & "]"
  21.         End
  22.     End If
  23. End Sub
½Æ»s¥N½X
±¡§Î2,¦b¥H¤U¤¤±Æ§Ç,«o¤@ª½±Æ¿ù,¬O­þ¸Ì¥X°ÝÃD¡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. '*********************************************************
  8.     With ¨Ó·½ÀÉ.Sheets(1)
  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.     End With
  15.     With ¥ØªºÀÉ.Sheets("®w¦s")
  16.         .Cells.Sort Key1:=.Columns("AD"), Key2:=.Columns("G"), Header:=xlYes
  17.     End With
  18.     ¨Ó·½ÀÉ.Close False
  19.     ¥ØªºÀÉ.Save
  20. End Sub
  21. '**********¨Ó·½ÀɬO¦PVBA³øªí«ü¥Oªº¸ê®Æ§¨\FromERP\*********
  22. Sub File_settings(xFile As Workbook, ¤u§@­¶ As String) 'Àɮ׳]©w
  23.     Dim xPath As String
  24.     xPath = ThisWorkbook.Path & "\"
  25.     If UCase(¤u§@­¶) <> UCase("ERP_Data.XLSX") Then xPath = xPath & "FromERP\"
  26.     On Error Resume Next
  27.     Set xFile = Workbooks(¤u§@­¶)
  28.     If Err > 0 Then Set xFile = Workbooks.Open(xPath & ¤u§@­¶)
  29.     If xFile.Name = "" Then
  30.         MsgBox "½Ð¬d¬Ý " & vbLf & xPath & vbLf & "¬O§_¦³ [" & ¤u§@­¶ & "]"
  31.         End
  32.     End If
  33. End Sub
½Æ»s¥N½X

TOP

¦^´_ 9# GBKEE
§ä¤F4¤Ñ,²×©óµo²{¤£¯àsortingªº­ì¦]...

TOP

        ÀR«ä¦Û¦b : ·R¤£¬O­n¨D¹ï¤è¡A¦Ó¬O­n¥Ñ¦Û¨­ªº¥I¥X¡C
ªð¦^¦Cªí ¤W¤@¥DÃD