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

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

¦^´_ 6# PJChen
.Range(Rng(1), Rng(2)).Resize(, 12).Copy Destination:=Sheets(2).Range("A1")
³oSheets(2) ¬O­þ­Ó¬¡­¶Ã¯ªº²Ä2­Ó¤u§@ªí
PKG.xlsx , Shipping VBA.xlsm ³£§ä¤£¨ì ³oµ{¦¡¥i¥H°õ¦æ¶Ü?

TOP

¦^´_ 10# c_c_lai
ÁÂÁ§A³o»ò¼ö¤ß,§A±N¥¨¶°¤¤ªº¥H¤U³o3¦æÅܦ¨¨S¦³§@¥Î,§Úª¾¹D³o¤T¦æ¨ú®ø«á¦³¤½¦¡ªº¦a¤è´N¤£·|¶Ã½X,¦ý³o¤£¬O§Ú­nªº,
    ' Sheets("PKG").Select
    ' Columns("R:AC").Select
    ' Selection.Delete Shift:=xlToLeft
­«ÂIÀ³¸Ó©ñ¦b            .Range(Rng(1), Rng(2)).Resize(, 11).Copy   ....³o¦æ°õ¦æ¤F¥H«á¥u¦³copy¨S¦³¿ìªk¦b¿ï¨úªº½d³ò¤¤¶K¤W­È,¥u­n³o­Ó¥i¥H"°_§@¥Î",¨ä¥¦¦³¤½¦¡ªº³¡¥÷´N³£¤£¬O°ÝÃD,¦Ü©ó¥H¤Uªº6¦æ¬O§Ú¤@©w­n°õ¦æªº,¤£¯à§R°£.
    Sheets("PKG").Select
     Columns("R:AC").Select
     Selection.Delete Shift:=xlToLeft
    Columns("A:C").Select
    Range("C11").Activate
    Selection.Delete Shift:=xlToLeft

TOP

¦^´_ 11# GBKEE
VBA TEST.zip (35.83 KB)
Hi,

§Ú±N³o­Óµ{¦¡¤À¶¥¬q°õ¦æ¤F«Ü¦h¦¸,ª¾¹D³o­Ó¦®¥O¨S¦³¼g§¹,¥¦¥u¨ìcopy¨Ã¨S¦³¦b­ì«ü©w½d³ò¤¤¶K¤W­È,¦Ó¥B¥H¤U³o¼Ë°µ¨Ã¤£¦X¥G§Ú·Q­n¥H"«ü©w¤å¦r"§@¬°³]©wªº½d³ò,     .Range(Rng(1), Rng(2)).Resize(, 12).Copy Destination:=Sheets(2).Range("A1")
½ÐÀ°§Ú¬Ý¬Ýªþ¥ó,§Ú¤w±N¥¨¶°µ{¦¡ copy¶i¥h,¥u¬O¤£ª¾¹D.Range(Rng(1), Rng(2)).Resize(, 12).Copy¤§«á¦p¦ó°µ?
§Ú¤£¬O­n¦b¨ä¥¦ªºsheet    .Range("A1")¤¤¶K¤W­È,¦Ó¬O­nÅý¥¦¦b­ì³]©wªº½d³ò¶K¤W­È,½ÐÀ°À°¦£.

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-4-28 21:37 ½s¿è

¦^´_ 13# PJChen

½Ð¨Ì·Ó¥Ø«ePKG²{¦³¸ê®Æ¡A§A·Q­nªº³Ì«áµ²ªG¥Î¤â°Ê¾Þ§@§¹¦¨¤W¶Ç
¦]¬°¥H§A¿ù»~ªºµ{¦¡½X¬OµLªk¤F¸Ñ§A­nªº¥Øªº¬°¦ó
¦pªG³æ¯Â±N¸Ó½d³òÂন­È
  1. Sub Try()

  2. With Workbooks("PKG.xlsx")
  3.     With .Sheets("PKG")
  4.         Set a = .Range("D:D").Find("Shipped per SS:", LOOKAT:=xlPart)
  5.         Set b = .Range("D:D").Find("PACKING:")
  6.         If a Is Nothing Or b Is Nothing Then
  7.             MsgBox "§ä¤£¨ì"
  8.         Else
  9.             .Range(a, b.Offset(, 11)) = .Range(a, b.Offset(, 11)).Value
  10.         End If
  11.     End With
  12. End With
  13. End Sub
½Æ»s¥N½X
¦Ü©ó¨ä¥L°Ê§@½Ð´y­z²M·¡
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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)
            .Range(a.Offset(-11, 0), b.Offset(, 12)) = .Range(a.Offset(-11, 0), b.Offset(, 12)).Value

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 18# Hsieh

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

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-4-29 06:42 ½s¿è

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

  1. Sub Try()
  2.     Dim Rng(1 To 2) As Range
  3.    
  4.     Windows("PKG.xlsx").Activate

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

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD