[µo°Ý] «ü©wD3Àx¦s®æ,°õ¦æ±Æ§Ç

PJChen

½Ð±Ð¤j¤j,

§Ú¦b¤u§@ªísorting.xlsmªºD3Àx¦s®æ¿é¤J¤@­Ó¤u§@ªí¦WºÙ(¦]¬°»Ý±Æ§Çªº¤u§@ªí¦³«Ü¦h,·Q¥Î°ÊºA¤èªk),¥H«ü©wD3¤u§@ªí¦WºÙ°õ¦æ±Æ§Çªº¥¨¶°µ{¦¡,¦ý¤@ª½µLªk¹B§@,
¤W¶Çªþ¥ó,¥i§_«üÂI¿ù»~!
±Æ§Ç.zip (98.38 KB)

¤U¸ü¦¸¼Æ: 16

2012-10-24 11:32

GBKEE

¦^´_ 1# PJChen
¸Õ¸Õ¬Ý

1. Sub sorting()
   
2.       Workbooks(Workbooks("sorting.xlsm").Worksheets("±Æ§Ç").Range("D3").Value).Activate '¥ý²¾¨ì³o¬¡­¶Ã¯
   
3.       ''''''    .Activate '­n¸Ñ½Xªº¤u§@ªí§ï¦¨¦bD3¿é¤J
   
4.     With ActiveSheet          ' With §ï¦b³o¸Õ¸Õ¬Ý
   
5.         Set b = .Range("R4").CurrentRegion
   
6.     '  ¨ä¾l2007 Äݩʤèªk 2003 ¤¤µLªk¨Ï¥Î
   
7.     '

½Æ»s¥N½X

PJChen

¦^´_ 2# GBKEE

ÁÂÁÂG¤j,

¦¨¥\¤F!!

c_c_lai

¦^´_ 3# PJChen
¨Ì¾ÚGBKEE¤j¤jªºµ{¦¡½X¤]¥i¥H­×§ï¦¨¡G

1. Sub sorting()
   
2.     Dim Wb As Workbook
   
3.    
   
4.     '  Workbooks(Workbooks("sorting.xlsm").Worksheets("±Æ§Ç").Range("D3").Value).Activate
   
5.     '  ­n¸Ñ½Xªº¤u§@ªí§ï¦¨¦bD3¿é¤J¡A°õ¦æ®É¤@¨Ö­n¦P®É¶}±Ò 2012 BCMart Chart-Adam 20121023.xls
   
6.     '  ¤U¦C­×¥¿¬°°õ¦æ sorting.xlsm ®É¡A ¨t²Î·|¦Û°Ê³s¦P±N 2012 BCMart Chart-Adam 20121023.xls ¤@¨Ö¦P®É¶}±Ò¡C
   
7.     Set Wb = Workbooks.Open(ThisWorkbook.Path & "\" & Workbooks("sorting.xlsm").Worksheets("±Æ§Ç").Range("D3").Value)
   
8.    
   
9.     With Wb.ActiveSheet
   
10.         Set b = .Range("R4").CurrentRegion
    
11.         A = Array("R", "S", "T", "Q", "D")
    
12.         .AutoFilter.Sort.SortFields.Clear
    
13.         For i = 0 To 4
    
14.            .AutoFilter.Sort.SortFields.Add Key:=b.Columns(A(i)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
15.         Next
    
16.         With .AutoFilter.Sort
    
17.             .Header = xlYes
    
18.             .MatchCase = False
    
19.             .Orientation = xlTopToBottom
    
20.             .SortMethod = xlPinYin
    
21.             .Apply
    
22.         End With
    
23.     End With
    
24. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 4# c_c_lai
·íÀɮשñ¦b¦P¤@¸ê®Æ§¨®É,³o¼Ë½T¹ê¤]¬O¤@ºØ¤è«Kªº§@·~¤èªk, ÁÂÁ§Aªº¥©«ä!

PJChen

¦^´_ 2# GBKEE

¤j¤j,
§Ú·Q±N«ü©wÀx¦s®æªºidea¨Ì¼Ëµe¸¬ÄªÀ³¥Î¨ìñ¦W¤W,
¦b"¶Kñ¦W.xlsm"ªº"EX" sheetªº«ü©wÀx¦s®æD3/ G3¤ºªºÀɦW,°õ¦æ"¶Kñ¦W"ªº°Ê§@,¦ý¤@ª½¥X²{¿ù»~

¤U¸ü (22.54 KB)

2012-11-2 23:27

¥¨¶°µ{¦¡¤]ªþ¤W,¥i§_À°¦£¬Ý¬Ý? ·PÁÂ!
¶Kñ¦W__«ü©wÀx¦s®æ¤ºªºÀɦW¬°¹ï¶H.zip (129.63 KB)

¤U¸ü¦¸¼Æ: 6

2012-11-2 23:29

GBKEE

¦^´_ 6# PJChen
½Ð¸Ô¬Ý µù¸Ñ

1. Sub copy_signed()
   
2.     Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value).Activate '¥ý²¾¨ì³o¬¡­¶Ã¯
   
3.     '***¤£¥Î¥ý²¾¨ì³o¬¡­¶Ã¯:³oµ{¦¡¬O¦bWorkbooks("¶Kñ¦W.xlsm").Worksheets("EX")¤¤©I¥s©Ò¥H¬O¦h¾lªºµ{¦¡½X.
   
4.     Dim Rng(1 To 3) As Range, xi As Integer
   
5.     Workbooks("¶Kñ¦W.xlsm").Sheets("Signed").Pictures("Picture 1").Copy
   
6.     With ActiveSheet          '**¥iµù¸Ñ±¼   '***  With §ï¦b³o¸Õ¸Õ¬Ý  <= ¤£¬O§i¶D§A§ï¤@§ï  ****
   
7.     .Activate                 '**¥iµù¸Ñ±¼
   
8.     With Workbooks("Shipping for ACE.xlsx")  '***§ï¤@§ï¦¨³o¼Ë:«e­±ªºWith ActiveSheet´N¨S§@¥Î
   
9.                 '*** .Sheets("PKG") ³oÂI¦b With ActiveSheet ¬O¿ù»~ªº ¤@¼Ë³£¬O¤u§@ªí
   
10.         Set Rng(1) = .Sheets("PKG").[r:r].Find("B. C. MART COMPANY LTD.", LOOKAT:=xlPart).Offset(2, -2)
    
11.                         '** ­ì¬O«ü©w[S:S]·|·j¯Á¤£¨ìªº   **
    
12.         Set Rng(2) = .Sheets("INV").[Q:Q].Find("B. C. MART COMPANY LTD.").Offset(2, -2)
    
13.         Set Rng(3) = .Sheets("SCD").[B:B].Find("Signature:").Offset(1, 1)
    
14.        End With
    
15.         For xi = 1 To 3
    
16.             Rng(xi).Parent.Activate
    
17.             Rng(xi).Activate
    
18.             ActiveSheet.Paste
    
19.         Next
    
20.     End With  '** ¥iµù¸Ñ±¼
    
21. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 7# GBKEE

¤j¤j,

µ{¦¡­×§ï¥H«á¬°¥H¤U,¦ý§Ú¤£¬O§Úªº­ì·N,§Ú§Æ±æ±Nµ{¦¡¤¤ªº("Shipping for ACE.xlsx") ´À´«±¼,­n¦p¦ó¥H"¶Kñ¦W.xlsm"ªº"EX" sheetªº«ü©wÀx¦s®æD3/ G3¤ºªºÀɦW,¨ú¥Nµ{¦¡¤¤ªºÀɦWWith Workbooks("Shipping for ACE.xlsx")  ?

Sub copy_signed()
    Dim Rng(1 To 3) As Range, xi As Integer
    Workbooks("¶Kñ¦W.xlsm").Sheets("Signed").Pictures("Picture 1").Copy
    With Workbooks("Shipping for ACE.xlsx")  '***§ï¤@§ï¦¨³o¼Ë:«e­±ªºWith ActiveSheet´N¨S§@¥Î
        Set Rng(1) = .Sheets("PKG").[r:r].Find("B. C. MART COMPANY LTD.", LOOKAT:=xlPart).Offset(2, -2)
        Set Rng(2) = .Sheets("INV").[Q:Q].Find("B. C. MART COMPANY LTD.").Offset(2, -2)
        Set Rng(3) = .Sheets("SCD").[B:B].Find("Signature:").Offset(1, 1)
       End With
        For xi = 1 To 3
            Rng(xi).Parent.Activate
            Rng(xi).Activate
            ActiveSheet.Paste
        Next
    End Sub

GBKEE

¦^´_ 8# PJChen
¬O³o¼Ë¶Ü?

1. Sub copy_signed()
   
2.     Dim Rng(1 To 3) As Range, xi As Integer
   
3.     Workbooks("¶Kñ¦W.xlsm").Sheets("Signed").Pictures("Picture 1").Copy
   
4.     With Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)  '§ï³o¸Ì...
   
5.         Set Rng(1) = .Sheets("PKG").[r:r].Find("B. C. MART COMPANY LTD.", LOOKAT:=xlPart).Offset(2, -2)
   
6.         Set Rng(2) = .Sheets("INV").[Q:Q].Find("B. C. MART COMPANY LTD.").Offset(2, -2)
   
7.         Set Rng(3) = .Sheets("SCD").[B:B].Find("Signature:").Offset(1, 1)
   
8.         For xi = 1 To 3
   
9.             Rng(xi).Parent.Activate
   
10.             Rng(xi).Activate
    
11.             ActiveSheet.Paste
    
12.         Next
    
13.     End With
    
14. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 8# PJChen
¦^´_ 9# GBKEE
¤W¥b³¡¦p§ï¦¨¦p¤U¡A­Ó¤Hı±o·|¤è«K¦h¡C

1. Sub copySigned()    ' GBKEE
   
2.     Dim Rng(1 To 3) As Range, xi As Integer, Wb As Workbook
   
3.    
   
4.     Set Wb = Workbooks.Open(ThisWorkbook.Path & "\" & Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)
   
5.    
   
6.     Workbooks("¶Kñ¦W.xlsm").Sheets("Signed").Pictures("Picture 1").Copy
   
7.     '   With Workbooks(Workbooks("¶Kñ¦W.xlsm").Worksheets("EX").Range("D3").Value)        '    §ï³o¸Ì...
   
8.     With Wb
   
9.          .
   
10.          .
    
11.     End With
    
12.          .

½Æ»s¥N½X

GBKEE ¤j¤j¡A¶¶«K½Ð±Ð¡A¦pªG "Picture 1"¡B"Picture 2" §Ú¬O¦b¸Ó¥Ø¿ý¤U¦s¦¨¨â­Ó .JPG ©Î¬O .BMP ÀÉ¡A
¨º¤S¸Ó¦p¦ó¥s¶i¨Ó©O¡H ÁÂÁ±z¡I