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

©P¤j°¶

¦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

joey0415

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

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

lpk187

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


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

©P¤j°¶

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

lpk187

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

¨SÀÉ®×¥i¥H¸Õ¡A¤]¤£ª¾¹ï¤£¹ï¡I

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

½Æ»s¥N½X

©P¤j°¶

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