[µo°Ý] ¥H"«ü©w¦r"§@¬°copy¨Ã¶K¤W­Èªº«ü©w½d³ò

PJChen

¥¨¶°¤¤ªº«ü©w½d³ò§@copy¨Ã¶K¤W­Èªº°Ê§@,¯à§_¥H"«ü©w¦r"§@¬°copy¨Ã¶K¤W­Èªº«ü©w½d³ò?³o­Ó¥Î·N¬O¦b©ó¤u§@ªíªº¸ê®Æ¦³¼W¥[©Î´î¤Ö®É,¥Î"«ü©w¦r"§@¬°½d³ò¤~¤£»Ý­n¤£Â_ªº¥h­×§ï¥¨¶°µ{¦¡,½Ð¤j®aÀ°À°¦£!
¨Ò¦p:D19:O133
¥H"Shipped per SS: "¦r¼Ë¬°¶}©l(D19),¥H"PACKING:"¦V¥k+11­ÓÀx¦s®æ(§YO133)¬°µ²§ô
PKG.zip (19.34 KB)

¤U¸ü¦¸¼Æ: 10

2012-4-28 15:07

JOYARK

ÁÂÁ´£¨Ñ¾Ç²ß«D±`¹ê¥Î

c_c_lai

¦^´_ 21# PJChen
Please check the CI12-460_ACE PO#120309 by FEDEX to .xlsx produced  this morning
in D:\Shipping Doc\PJ subdirectory.

PJChen

¦^´_ 20# c_c_lai
µ²ªG¬Ý¨Ó¬O¬Û¦Pªº.

c_c_lai

¦^´_ 19# PJChen
³o¬O¤£¬O©p­nªº¡H

¤U¸ü (90.85 KB)

2012-4-29 06:32

¤U¸ü (94.91 KB)

2012-4-29 06:33

1. Sub Try()
   
2.     Dim Rng(1 To 2) As Range
   
3.    
   
4.     Windows("PKG.xlsx").Activate
   
5. 
   
6.      '  ¥[¤J Hsieh ¥ý¶iªº¤Þ¥Î¤èªk
   
7.     With Workbooks("PKG.xlsx")
   
8.         With .Sheets("PKG")
   
9.             Set Rng(1) = .Range("D:D").Find("Shipped per SS:", LOOKAT:=xlPart)
   
10.             Set Rng(2) = .Range("D:D").Find("PACKING:")
    
11.             
    
12.             If Rng(1) Is Nothing Or Rng(2) Is Nothing Then
    
13.                 MsgBox "§ä¤£¨ì"
    
14.             Else     '  ¨Ì·Ó Hsieh ¥ý¶iªº«ü¥Ü¡Aµy¥[­×¹¢
    
15.                .Range(Rng(1), Rng(2).Offset(, 11)) = .Range(Rng(1), Rng(2)).Resize(, 12).Value
    
16.             End If
    
17.         End With
    
18.     End With
    
19.    
    
20.    
    
21.     Selection.Copy
    
22.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    
23.         :=False, Transpose:=False
    
24.     Range("Q122:Q122").Select
    
25.     Selection.Copy
    
26.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    
27.         :=False, Transpose:=False
    
28.     ActiveSheet.Paste
    
29.     Rows("143:143").Select
    
30.     Selection.ClearContents
    
31.     Range("C1").Select
    
32.    
    
33.     ActiveWorkbook.SaveAs "D:\Shipping Doc\PJ\" & [Q5] & "_" & [C6] & " PO#" & [V7] & " by " & [C19] & " to " & [C22] & ".xlsx"
    
34.         
    
35.     Sheets("PKG").Select                         ' «ì´_­ìª¬
    
36.     Columns("R:AC").Select                    ' «ì´_­ìª¬
    
37.     Selection.Delete Shift:=xlToLeft     ' «ì´_­ìª¬
    
38.     Columns("A:C").Select
    
39.     Range("C11").Activate
    
40.     Selection.Delete Shift:=xlToLeft
    
41.    
    
42.     ActiveWorkbook.Save
    
43. End Sub

½Æ»s¥N½X

VBA TEST.rar (55.82 KB)

¤U¸ü¦¸¼Æ: 7

2012-4-29 06:39

PJChen

¦^´_ 18# Hsieh

ÁÂÁª©¤j,
­ì¨Ó¬O¦Û¤w¤j·N

Hsieh

¦^´_ 17# PJChen


    D19¬OÅܼÆaªº¦ì¸m¡A¦V¤W11¦C¤]¥u¨ìD8
§A©¿²¤¤F14¨ì18¦C³QÁôÂáA¨Sºâ³o4¦C©Ò¥H½d³ò¨S¥]§tD4

PJChen

¦^´_ 16# Hsieh
¤£¦n·N«äª©¤j,
¥Ñ"Shipped per SS:"¦V¤W11®æ(Àx¦s®æD4)¦b§Úªº¤u§@ªí¤W¬O¨S¦³¶W¥X½d³ò,¦ý§Ú¬G·N¦bD4©ñ¸m¤F¤½¦¡,­×§ï¬°¥H¤Uªº¼Ë¤l,
¶K¤W­È¤§«áD4¤½¦¡ÁÙ¦b,©Ò¥H  .Range(a.Offset(-11, 0)¨Ã¨S¦³¦V¤W11®æ,§Ú¸Ó«ç»ò¿ì?
¦pªG§A»Ý­n¬ÝÀÉ®×,§Ú¤W¶Ç¤F   VBA TEST.zip (35.29 KB)

¤U¸ü¦¸¼Æ: 4

2012-4-28 22:23

            .Range(a.Offset(-11, 0), b.Offset(, 12)) = .Range(a.Offset(-11, 0), b.Offset(, 12)).Value

Hsieh

¦^´_ 15# PJChen

¦³2­Ó°ÝÃD
1.¦V¤W11®æ¦¡Offset(-11,0)
2.¦pªGaªº¦ì¸m¦b1¨ì11¦C¤§¶¡¡A¨º»ò¡A¦V¤W11¦C´N·|¶W¥X¤u§@ªí½d³ò¡A©Ò¥H¥X¿ù¡C

PJChen

¦^´_ 14# Hsieh

Áª©¤j,
°õ¦æ«á¨S¦³°ÝÃD,¦ý½Ð«ü±Ð¤@­Ó½d³ò¤Wªº°ÝÃD:
bªº½d³ò³o¼Ë¼g¥i¥H¦V¥k©µ¦ù12®æ....... b.Offset(, 12)
¨º¦pªGaªº½d³ò§Ú·Q¦V¤W¨«11®æ,§Ú§ï¬°¥H¤U,¬°¤°»ò¥¦¤£¯à°õ¦æ?
.Range(a.Offset(, -11), b.Offset(, 12)) = .Range(a.Offset(, -11), b.Offset(, 12)).Value