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

¥i§_¨ó§U²¤Æµ{¦¡

¥i§_¨ó§U²¤Æµ{¦¡

¦U°ª¤H, ¦n
¤p§Ì¿ý¨î¤F¤@­Óµ{¦¡, ¨Ï¥Î¨S¦³°ÝÃD, ¦ýµ{¦¡»áªø, ¦Ó¨Ï¥Î®É¤¶­±°{°Ê, ¦¹¿ý¨îµ{¦¡¥i¦³Â²¤ÆªÅ¶¡, ½Ð°ª¤H­Ì¨ó§U, ÁÂÁÂ!!

Sub §ó·s()
'
' ¸ê®Æ§ó·s ¥¨¶°
'

'
    Windows("abc­Ü®w¸ê®Æ.xlsm").Activate
    Sheets("Á`¤½¥q­Ü®w").Select
    Range("J4").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!C7,[´£¨ú³æ.xlsm]´£¨ú³æ!C15,RC7,[´£¨ú³æ.xlsm]´£¨ú³æ!C3,RC2)+SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!R3C7,[´£¨ú³æ.xlsm]´£¨ú³æ!R3C15,RC[-3],[´£¨ú³æ.xlsm]´£¨ú³æ!R3C3,RC[-8])+SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!R3C7,[´£¨ú³æ.xlsm]´£¨ú³æ!R3C15,RC[-3],[´£¨ú³æ.xlsm]´£¨ú³æ!R3C3,RC[-8])"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS([¤J³f³æ.xlsm]Sheet1!C8,[¤J³f³æ.xlsm]Sheet1!C13,RC7,[¤J³f³æ.xlsm]Sheet1!C3,RC2)+SUMIFS([¤J³f³æ.xlsm]Sheet1!R3C8,[¤J³f³æ.xlsm]Sheet1!R3C13,RC[-4],[¤J³f³æ.xlsm]Sheet1!R3C3,RC[-9])"
    Range("L4").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]+RC[-1]"
    ActiveWorkbook.Save
    Range("J4:L4").Select
    Selection.AutoFill Destination:=Range("J4:L22"), Type:=xlFillDefault
    Range("J4:L22").Select
    Range("L22").Select
    Sheets("¤À¤½¥q­Ü®w").Select
    Range("J4").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!C7,[´£¨ú³æ.xlsm]´£¨ú³æ!C15,RC7,[´£¨ú³æ.xlsm]´£¨ú³æ!C3,RC2)+SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!R3C7,[´£¨ú³æ.xlsm]´£¨ú³æ!R3C15,RC[-3],[´£¨ú³æ.xlsm]´£¨ú³æ!R3C3,RC[-8])"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS([¤J³f³æ.xlsm]Sheet1!C8,[¤J³f³æ.xlsm]Sheet1!C13,RC7,[¤J³f³æ.xlsm]Sheet1!C3,RC2)+SUMIFS([¤J³f³æ.xlsm]Sheet1!R3C8,[¤J³f³æ.xlsm]Sheet1!R3C13,RC[-4],[¤J³f³æ.xlsm]Sheet1!R3C3,RC[-9])"
    Range("L4").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]+RC[-1]"
    ActiveWorkbook.Save
    Range("J4:L4").Select
    Selection.AutoFill Destination:=Range("J4:L22"), Type:=xlFillDefault
    Range("J4:L22").Select
    Range("L22").Select
     Sheets("Á`¤½¥q­Ü®w").Select
    Range("J4:L22").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L4").Select
    Application.CutCopyMode = False
    Sheets("¤À¤½¥q­Ü®w").Select
    Range("J4:L22").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L4").Select
    Application.CutCopyMode = False
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    Windows("´£¨ú³æ.xlsm").Activate
    Range("A2").Select
End Sub

¦^´_ 1# ©P¤j°¶

¤À¬q¿ý¨î¡A¤À¬q²¤Æ¡A§A·|§ó¦³¦¨´N·P

TOP

¦^´_ 1# ©P¤j°¶


    ª©¥Dªº³o½g¥i¥H°Ñ¦Ò
http://forum.twbts.com/viewthrea ... p%3Bfilter%3Ddigest

TOP

¥»©«³Ì«á¥Ñ ©P¤j°¶ ©ó 2015-6-24 12:51 ½s¿è

¦^´_ 3# lpk187                                                                     ¤p§Ì©ÒÃѦ³­­¡A¤W­z´£¤Î¤å³¹¤]´¿°Ñ¦Ò¡A¦ý¥\¤O¤Ó²L¡A¬G§â¦¹¿ý¨î¥¨¶°¤W¶Ç¡A§Æ±æ±o¨ì¦^À³¡A³Ì«áÁÂÁ´£¥Ü¡A¯¬§Ö¼Ö¡C ¦¹¤å¤â¾÷ÂСA¦rÅé¦ì¸m°¾Â÷¡A¨£½Ì¡C

TOP

¦^´_ 4# ©P¤j°¶

¨SÀÉ®×¥i¥H¸Õ¡A¤]¤£ª¾¹ï¤£¹ï¡I
  1. Sub §ó·s()
  2. '
  3. ' ¸ê®Æ§ó·s ¥¨¶°
  4. '

  5. '
  6.     Windows("abc­Ü®w¸ê®Æ.xlsm").Activate
  7.     Sheets("Á`¤½¥q­Ü®w").Select
  8.     Range("J4") = "=SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!C7,[´£¨ú³æ.xlsm]´£¨ú³æ!C15,RC7,[´£¨ú³æ.xlsm]´£¨ú³æ!C3,RC2)+SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!R3C7,[´£¨ú³æ.xlsm]´£¨ú³æ!R3C15,RC[-3],[´£¨ú³æ.xlsm]´£¨ú³æ!R3C3,RC[-8])+SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!R3C7,[´£¨ú³æ.xlsm]´£¨ú³æ!R3C15,RC[-3],[´£¨ú³æ.xlsm]´£¨ú³æ!R3C3,RC[-8])"
  9.     Range("K4") = "=SUMIFS([¤J³f³æ.xlsm]Sheet1!C8,[¤J³f³æ.xlsm]Sheet1!C13,RC7,[¤J³f³æ.xlsm]Sheet1!C3,RC2)+SUMIFS([¤J³f³æ.xlsm]Sheet1!R3C8,[¤J³f³æ.xlsm]Sheet1!R3C13,RC[-4],[¤J³f³æ.xlsm]Sheet1!R3C3,RC[-9])"
  10.     Range("L4") = "=RC[-3]-RC[-2]+RC[-1]"
  11.     ActiveWorkbook.Save
  12.     Range("J4:L4").AutoFill Destination:=Range("J4:L22"), Type:=xlFillDefault
  13.     Range("L22").Select
  14.     Sheets("¤À¤½¥q­Ü®w").Select
  15.     Range("J4") = "=SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!C7,[´£¨ú³æ.xlsm]´£¨ú³æ!C15,RC7,[´£¨ú³æ.xlsm]´£¨ú³æ!C3,RC2)+SUMIFS([´£¨ú³æ.xlsm]´£¨ú³æ!R3C7,[´£¨ú³æ.xlsm]´£¨ú³æ!R3C15,RC[-3],[´£¨ú³æ.xlsm]´£¨ú³æ!R3C3,RC[-8])"
  16.     Range("K4") = "=SUMIFS([¤J³f³æ.xlsm]Sheet1!C8,[¤J³f³æ.xlsm]Sheet1!C13,RC7,[¤J³f³æ.xlsm]Sheet1!C3,RC2)+SUMIFS([¤J³f³æ.xlsm]Sheet1!R3C8,[¤J³f³æ.xlsm]Sheet1!R3C13,RC[-4],[¤J³f³æ.xlsm]Sheet1!R3C3,RC[-9])"
  17.     Range("L4") = "=RC[-3]-RC[-2]+RC[-1]"
  18.     ActiveWorkbook.Save
  19.     Range("J4:L4").AutoFill Destination:=Range("J4:L22"), Type:=xlFillDefault
  20.     Sheets("Á`¤½¥q­Ü®w").Select
  21.     Range("J4:L22").Copy
  22.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  23.         :=False, Transpose:=False
  24.     Range("L4").Select
  25.     Application.CutCopyMode = False
  26.     Sheets("¤À¤½¥q­Ü®w").Select
  27.     Range("J4:L22").Copy
  28.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  29.         :=False, Transpose:=False
  30.     Range("L4").Select
  31.     Application.CutCopyMode = False
  32.     ActiveWindow.ScrollRow = 5
  33.     ActiveWindow.ScrollRow = 4
  34.     Windows("´£¨ú³æ.xlsm").Activate
  35.     Range("A2").Select
  36. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# lpk187
ÁÂÁ¦^À³, ¯¬Ä@§Ö¼Ö..

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD