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

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

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

½Ð±Ð¤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)

¦^´_ 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

TOP

¦^´_ 2# GBKEE

ÁÂÁÂG¤j,

¦¨¥\¤F!!

TOP

¦^´_ 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

TOP

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

TOP

¦^´_ 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²{¿ù»~


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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-11-5 09:44 ½s¿è

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD