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

[µo°Ý] ¹ïÀ³¤é´Á¶K½LÂI¸ê®Æ

[µo°Ý] ¹ïÀ³¤é´Á¶K½LÂI¸ê®Æ

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-7-4 13:36 ½s¿è

¤j¤j­Ì¦n,
    With Sh
    Sh.Activate
        For j = 6 To xrow Step 2
            .Range("X" & j + 1 + 1).Resize(1, 16).Copy
            xW.Sheets("½LÂI").Range("BO" & 1 + j).Resize(1, 16).PasteSpecial xlPasteValues
        Next
    End With

½LÂI¤u§@ªí±qAÄ檺¦U«È¤áÀɮקâ¬ÛÀ³ªº½LÂI¸ê®Æ,copy¹L¨Ó
E4¬O«ü©w¤é´Á
AÄæ¬O«È¤á¦W
·íDIÄæ= D+0«h±q«È¤áÀɮתº¼Æ¦r¤u§@ªí§äE4-1ªº¤u§@ªí
ex:E4=7/3,«h§ä"2"¤u§@ªí
±Nx:amªº½LÂI¸ê®Æ,¦³®w¦sªº¼Æ¦r,¹ïÀ³¤é´Á¬Û²Å,¶K¨ì½LÂI¤u§@ªíªºBH:CFªºÄæ¦ì¤¤

¨Ó·½¦U«È¤áªºÀɮ׮榡¤£·|§¹¥þ¬Û¦P,¦ý³£¬OÃþ¦üªº,¥u¥H¤@©w§@½d¥»,
½LÂI¤u§@ªí¤¤¦³«Ü¦hªº¤½¦¡,©Ò¥H¶K¤Wªº¸ê®Æ¤£¯à¤zÂZ¨ä¥LÀx¦s®æ,
½Ð°Ý(¬õ¦r)¹ïÀ³¤é´Á¶K¤W½LÂI¼Æ­Èªº³o¬qµ{¦¡¸Ó«ç»ò¼g?   ¶K½LÂI¸ê®Æ.rar (328.03 KB)
  1. Sub copy_»e¸Á½LÂI()
  2. Dim PH$, FN$, W As Workbook, xW As Workbook, xD As Worksheet, xS As Worksheet, Sh As Worksheet, i As String, rng As Range
  3. Set xD = ThisWorkbook.Sheets("VBA")  'µ{¦¡¨Ó·½
  4. k = xD.[V1] - 1 '¨ú«e¤@¤é
  5. i = Format(k, "D")
  6. y = Format(k, "yyyy")
  7. m = Format(k, "m")
  8. '---------------------
  9. Set xW = Workbooks("¦h«È¤á½LÂIªí")
  10. PH = xD.[BB1]
  11. '---------------------
  12. FN = Dir(PH & "*»e¸Á*" & y & "*" & m & ".xlsx")
  13. Do While FN <> ""
  14. On Error Resume Next: Set W = Workbooks(FN): On Error GoTo 0
  15. If W Is Nothing Then Set W = Workbooks.Open(PH & FN)
  16. Set Sh = W.Sheets(i)
  17. With Sh
  18.     xrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 5
  19. End With
  20.      xW.Sheets("½LÂI").Range("M6:M" & xrow) = Sh.Range("T7:T" & xrow).Value '¨Ó·½ «e¤éµ²¾l   
  21. With Sh
  22.     Sh.Activate
  23.         For j = 6 To xrow Step 2
  24.             .Range("X" & j + 1 + 1).Resize(1, 16).Copy
  25.             xW.Sheets("½LÂI").Range("BO" & 1 + j).Resize(1, 16).PasteSpecial xlPasteValues
  26.         Next
  27.     End With
  28.    
  29. FN = Dir
  30. Loop
  31. End Sub
½Æ»s¥N½X

¦^´_ 1# PJChen

¦h«È¤á½LÂIªí.xlsx BH:GFÄæ¤é´Áªº¤ñ¹ï­n¥H¨ì´Á¤é(Âù¸¹¦C)¬°·Ç,¦C5¬O¥Í²£¤é,¤£­n§@¤ñ¹ï...

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-7-12 20:41 ½s¿è

¦^´_ 4# °a¤ªºµ

¦h«È¤á½LÂIªí ¬O¥Øªº¸ê®ÆÀÉ,±µ¦¬¦U«È¤áªº½LÂI¸ê®Æ
DIÄæ ªº 0 ¸ò 1 ¬O¦Û­qªº,
¨Ò¦p¤µ¤Ñ¬O7/12,DIÄæ ¥X²{ªº¬O0,«h¨Ó¦Û«È¤áªº½LÂI¸ê®Æ«h­n¶K12-1=11¤u§@ªíªº½LÂI¸ê®Æ,
DIÄæ ¥X²{ªº¬O1,«h¨Ó¦Û«È¤áªº½LÂI¸ê®Æ«h­n¶K12-0=12¤u§@ªíªº½LÂI¸ê®Æ,
³o­Ó¬O©T©wªº³W«h¼Ò¦¡,§¹¥þµLÅܤƪº,¬O¤½¥q§@·~ªº³W«h¦Ó¤w¡I
©Ò¦³ªºªí®æ³£¬O´ú¸Õ¥Î,¥i¥H¥ô·N¶ñ¤J¸ê®Æ,¥Î¨Ó°µµ{¦¡´ú¸Õ,
¤µ¤Ñ¬O7/12,§Ú¦b¨Ó·½Àɪº11¤u§@ªí,¥ô·N¶ñ¤J¨Ç¸ê®Æ,´ú¸Õµ{¦¡,µo²{§¹¥þµL¶K¤Wªº°Ê§@

¨Ó·½ªº«È¤á½LÂI®æ¦¡¤À2ºØÃþ«¬,¤@ºØ¬O2®æ¦X¨Ö(¥Øªº¤u§@ªí¬O¶K¦b"½LÂI"),¤@ºØ¬O3®æ¦X¨Ö(¥Øªº¤u§@ªí¬O¶K¦b"stock"),
¨Ó·½ªº«È¤á½LÂI¸ê®Æ¦³«Ü¦h­Ó,©Ò¥H°µ¦¨ªºµ{¦¡,§Æ±æ¯à¦Û¦æ«ü©w¨Ó·½ªº½LÂI°Ï(½LÂI°Ï¤£¤@©w¦b­þ­ÓÀx¦s®æ),´N¯à¶K¤W¸ê®Æ
²{±N¨Ó·½ªº«È¤á½LÂIÀÉ,¦Uªþ¤W¤@­Ó´ú¸ÕÀÉ
¶K½LÂI¸ê®Æ0710.rar (494.03 KB)

TOP

¦^´_ 6# °a¤ªºµ
stock ¸ò ¸U¹F®w¦sªíªº´ú¸ÕÀÉ,¤é´Á³£¬O´ú¸Õªº,©Ò¥H§ï¤@¤U¥i¥H´ú¸Õ´N¦n¤F ¶K½LÂI¸ê®Æ0710.rar (470.04 KB)
----------¸U¹F®w¦sªí ½LÂI¸ê®Æ
(§C¯×)U8:AF8,¹ïÀ³stock BH7:CF7
(­ì¨ý)U11:AF11,¹ïÀ³stock BH10:CF10
¦³¬Û¦P¤é´Áªº½LÂI¼Æ,´N¶K¨ìstock¦P¬Û¤é´Áªº½LÂI®æ¤º

----------»e¸Á­Ü ½LÂI¸ê®Æ
V7:AK7,V9:AK9,V11:AK11¦P«~¦W&¦P¤é´Á¹ïÀ³
¦h«È¤á½LÂIªí.sh(½LÂI)BH6:CF6,BH8:CF8,BH10:CF10

TOP

¦^´_ 8# °a¤ªºµ
ºµ¤j¦n,

´ú¸Õµ²ªG,Stockªº¤é´Á¨S¦³³sÄò®É,¶K¤Wªº½LÂI¸ê®Æ·|¤£¥¿½T,´ú¸Õµ²ªG¯d¦b¤u§@ªí¤¤,
§Ú¦³«Ü¦hÀÉ®×­n¥Î¨ì³o­Ó2­Óµ{¦¡,¥i§_À°¦£µù¸Ñµ{¦¡?
¶K½LÂI¸ê®Æ0710.rar (476.52 KB)

TOP

¦^´_ 10# °a¤ªºµ

¦Aªþ¤W2­Ó´ú¸ÕÀÉ ¶K½LÂI¸ê®Æ_´ú¸Õ2.rar (330.44 KB)
µ{¦¡§Ú¦h´ú¸Õ¨Ç¦A¦^ÂÐ
½Ð°Ýµ{¦¡³Æµù,¯à§_¦A¸Ô²Ó¨Ç?

TOP

¦^´_ 10# °a¤ªºµ
ºµ¤j,
½ÐÀ°¦£¬ã¨s¤@¤U,
a) ¥ØªºÀɦ³¤GºØ©T©w®æ¦¡,¥Bkey-inªºÄæ¦ì½d³ò¬Û¦P,¥u¦³¦X¨ÖÀx¦s®æ¤À2¦C&3¦C
b) ¦Ó¨Ó·½ÀÉÁöµM½LÂI½d³ò¤£¦P,¦ý®æ¦¡¤j­P¬Û¦P,¥u¦³¦C¼Æªº¤£¦P,¤]¤À¬°¤GºØ®æ¦¡
1) ½LÂI°Ï¦b(½L®t)(¹w³]¤Ñ¼Æ)¤§¶¡
2) ½LÂI°Ï¦b(µ²¾l¦X­p)(½L®t)¤§¶¡
¦³¨S¦³¥i¯àµ{¦¡¥i¥H¥u§ïÀɦW,´N¥i¥H¾A¥Î©ó±q¨Ó·½¶K¨ì¥ØªºÀÉ?
(³Ì¦h´N¤À¦¨2­Óµ{¦¡,¤À§O¬O¶K¨ì½LÂI¡Bstock)
¦]¬°«È¤á·|¦³¼W¥[ªº¥i¯à©Ê,«~¦W¤]¬O,
§Æ±æ¯à°÷¦b¼W¥[«È¤á&«~¦W®É,¥u­n¦b¤u§@ªí©¹¤U¼W¥[,´N¥i¥H¨Ï¥Î,
¤£­n¦A¥h­×§ïµ{¦¡¡I
¥t¥~ÁÙ·Q½Ð°Ý,ÀɦW¦]¬°³£«Üªø,§Ú·Q§ï¬°¶}ÀɮɥÎex: *¥Ã»·*,³o¼Ëªº¤èªk,
§Ú¥uª¾¹D³oºØ¼gªk,¤£ª¾ÁÙ¦³¨ä¥L¤è¦¡¶Ü?
   Do While File <> ""
        With Workbooks.Open(Path & File)
------
   File = Dir
Loop
ÂAªá®w¦sªí.rar (148.25 KB)

TOP

¦^´_ 13# °a¤ªºµ
ºµ¤j¦n,
1) stock¤u§@ªí,¤£¯à§ï¬°Sheets(¼Æ¦r),¹ê»Ú¨Ï¥ÎÀɧt¦³¨ä¥L¤u§@ªí,³o¼Ë·|¥X¿ù
2) stockªº¨Ó·½ÀÉ,¥u­n«~¦W¶¶§Ç¤£¦P,´NµLªk¶K¤W¸ê®Æ,µ{¦¡´N¤¤Â_¤F
3) Workbooks("¦h«È¤á½LÂIªí.xlsx").Sheets("½LÂI")¤]¬O¬Û¦P±¡§Î,¤£¯à§ï¬°Sheets(¼Æ¦r)
4) ¤½¥qªº¦s©ñ°Ï,¦³«Ü¦hÀÉ,¤£¥u¬O³o´X­ÓÀÉ®×,¥B¦³Ãþ¦üÀɦW,©Ò¥H¥ÎDir(U & "*.xlsx")¤]·|¥X¿ù
     ¤W¦¸¸ß°Ý:ÀɦW¦]¬°³£«Üªø,·Q§ï¬°¶}ÀɮɥÎex: *¥Ã»·*,³o¼Ëªº¤èªk,¤£ª¾¥i§_?
5) ¤G­Óµ{¦¡³£»Ý­n¦³¤ñ¹ï«~¦Wªº¥\¯à¤~¦æ,¤£µM¤@¥¹«~¦W±Æ¦C¤£¦P,´N·|¥X¿ù¤F
Re¶K½LÂI¸ê®Æ0716.rar (944.92 KB)

TOP

¦^´_ 20# °a¤ªºµ
³o­Óµ{¦¡ÀÉ,¥u¦³¤@­Óstockªºµ{¦¡,©Ò¥H´N¥ý´ú¸Õ³o­Ó
(§C¯×) 250gµLªk¶K¤W½LÂI¸ê®Æ

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-7-21 22:20 ½s¿è

¦^´_ 26# °a¤ªºµ

ºµ¤j¦n,
ªº´ú¸ÕÀÉ,³£¨S¤°»ò°ÝÃD,§Ú·|¦A¥Î¥¿¦¡ªºÀÉ®×´ú¬Ý¬Ý
¥t,§Ú·s¼W¤F¤@­Ó´ú¸ÕÀÉ,µo²{µLªk¹B§@,½ÐÀ°§Ú¬Ý¤@¤U
R_0721.rar (279.96 KB)

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD