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

[µo°Ý] ¤£¦Pªºsheet¬Û¦Pªº°Ê§@,¦p¦óÅý¥¦¤@¦¸©Ê§¹¦¨?

¦^´_ 41# oobird
Dear,
¥i¬O¯uªº³£¶]±o«ÜºC,¬O­ì¨Óªº¦n´X­¿®É¶¡©O!§Ú¤]«Ü¤£¤F¸Ñ¬°¤°»ò§ï¬°AA:AL·|Åܦ¨³o¼Ë?©ú©ú»Ý­nÅܦ¨­Èªº½d³òÅܤp¤F§r!

TOP

¦^´_ 42# PJChen
³y¦¨Àt³t¦³¥i¯à¬O¤½¦¡­«ºâµ²ªG
¦b«°¥«¶}©l¥ýÃö³¬¦Û°Ê­«ºâ
Application.Calculation = xlCalculationManual
¨ìµ{§Ç°h¥X«e¦A¶}±Ò¦Û°Ê­«ºâ
Application.Calculation = xlCalculationAutomatic
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 43# Hsieh
ª©¤j,
«ö±z»¡ªº¬O§_§ï¦¨¥H¤U³o¼Ë,µ²ªGÁÙ¬O¬Û¦P,§Ú¥Î½Xªí­p®É,¨CRUN¤@­Ó¤u§@ªí¬ù17~20¬í,³o¬O«ü²Ä¤@¬qµ{¦¡,²Ä¤G¬qµ{¦¡«h¬OµÏ¶¡´N§¹¦¨¤F,©ÎªÌ¥i§_½Ð¦³¿³½ìªº¤H³£À°¦£¬Ý¤@¤U,³o¤G¬q¬qµ{¦¡¦³¨S¦³¤°»ò®t§O?ÁÙ¥i¥H«ç»ò§ï?
¯u¹ï¤£°_°Õ!§Ú¬Ý¨ì³o­Ó³t«×²ª½...
  1. Sub Try_2()
  2.    Workbooks("2012 BCMart Chart.xlsx").Activate
  3.    Application.Calculation = xlCalculationManual
  4.    For s = 5 To 10 '±q²Ä¬Y­Ó¤u§@ªí°õ¦æ¦Ü²Ä¬Y­Ó
  5.    Sheets(s).Activate
  6.    Set sh = ActiveSheet
  7.    n = sh.[al10000].End(3).Row '(ALÄ榳¸ê®Æªº³Ì«á¦C)³o¸Ì¥i¥H§ï¦¨©pªº¸ê®Æ¤¤³Ì»ôªº¤@Äæ
  8.             sh.[aa:al] = sh.[aa:al].Value  '§â¤½¦¡µ²ªGÅܦ¨­È
  9.             sh.AutoFilterMode = False     '¨ú®ø¦Û°Ê¿z¿ï
  10.             sh.[a4:ad4].AutoFilter    '«Ø¥ß¦Û°Ê¿z
  11.     Next    '¤U¤@­Ó¤u§@ªí
  12.     Application.Calculation = xlCalculationManual
  13.     ActiveWorkbook.Save

  14.     Workbooks("2012 BCMart Chart.xlsx").Activate
  15.    For s = 5 To 10 '±q²Ä¬Y­Ó¤u§@ªí°õ¦æ¦Ü²Ä¬Y­Ó
  16.    Sheets(s).Activate
  17.    Set sh = ActiveSheet
  18.             n = sh.[AC1000].End(3).Row    'acÄæ³Ì«áÀx¦s®æ¦C¸¹
  19.             sh.Sort.SortFields.Clear    '²M°£­««Ø±Æ§Ç±ø¥ó
  20.             Ar = Array("ac", "u", "r", "s", "t", "q", "c", "d")
  21.             For i = 0 To UBound(Ar)
  22.                 sh.Sort.SortFields.Add Key:=sh.Range(Ar(i) & "5:" & Ar(i) & n)
  23.             Next
  24.             With sh.Sort    '¹ï«ü©w½d³ò¥H«ü©w±ø¥ó±Æ§Ç
  25.                 .SetRange sh.Range("A5:AD" & n)
  26.                 .Header = xlNo
  27.                 .MatchCase = False
  28.                 .Orientation = xlTopToBottom
  29.                 .SortMethod = xlPinYin
  30.                 .Apply
  31.             End With
  32.     Next    '¤U¤@­Ó¤u§@ªí
  33.     ActiveWorkbook.SaveAs "P:\BCMart Chart\2012 BCMart Chart-sorted.xlsx"
  34.    
  35. End Sub
½Æ»s¥N½X

TOP

Sub Try_2()
   Workbooks("2012 BCMart Chart.xlsx").Activate
   Application.Calculation = xlCalculationManual
   For s = 5 To 10 '±q²Ä¬Y­Ó¤u§@ªí°õ¦æ¦Ü²Ä¬Y­Ó
   Sheets(s).Activate
   Set sh = ActiveSheet
   n = sh.[al10000].End(3).Row '(ALÄ榳¸ê®Æªº³Ì«á¦C)³o¸Ì¥i¥H§ï¦¨©pªº¸ê®Æ¤¤³Ì»ôªº¤@Äæ
            sh.[aa5].Resize(n, 12) = sh.[aa5].Resize(n, 12).Value  '§â¤½¦¡µ²ªGÅܦ¨­È
            sh.AutoFilterMode = False     '¨ú®ø¦Û°Ê¿z¿ï
            sh.[a4:ad4].AutoFilter    '«Ø¥ß¦Û°Ê¿z
    Next    '¤U¤@­Ó¤u§@ªí
    Application.Calculation = xlCalculationManual
    ActiveWorkbook.Save

TOP

¦^´_ 45# oobird
¦^´_ 43# Hsieh
³ø§i ¤G¦ìª©¥D,
¸g¹L¬Q¤Ñoobirdª©¥Dªº´£ÂI,²{¦bµ{¦¡°õ¦æ³t«×§¹¥þ¥¿±`,·PÁ¦h¤é¨Ó¤£Ã㨯³ÒÀ°§Ú¸Ñ¨M°ÝÃD.

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD