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

[µo°Ý] ¤U­Ó¤ë·sÀÉ

[µo°Ý] ¤U­Ó¤ë·sÀÉ

Dear,
§Ú¦³¤@­Óµ{¦¡,¬O¬°¤F¥i¥H¦Û°Êrun¤U­Ó¤ëªº·sÀɮצӳ]
³Ìªñ·s¼W¤@¬qµ{¦¡,µ²ªGÅܦ¨¤@ª½´`Àô¶K¸ê®Æ
­Y§â¦³°ÝÃDªº³o¤@¬qµù¸Ñ,«hµ{¦¡´N¨S¦³°ÝÃD
½Ð ¤j¤j­ÌÀ°¦£¬Ý¤Uµ{¦¡...·PÁÂ

VB§@·~¤º®e:
¦b¸ê®Æ§¨¤¤¥´¶}Àɮ׸s
¿é¤J¤U­Ó¤ë1¤éªº¤é´Á,¦sÀɤ£Ãö³¬
­È¤Æ¤é´ÁÀx¦s®æ
§R°£¤j©ó¤ë©³¤éªº¤u§@ªí
¥t¦s¥Øªº¸ê®Æ§¨
¨ÌP1ªº¤u§@ªí¦WºÙ¼Æ¶q,¨Ì§Ç©R¦WÀɮצW¨Ã¤À§O¦sÀÉ
¥Øªº¤u§@ªí¥´¶},­È¤Æ¨C­Ó¤u§@ªíÀYªº¦WºÙ¨Ãclear­ì¤½¦¡
¨ÌP1ªº¤u§@ªí¦WºÙ¼Æ¶q,¨Ì§Ç©R¦WÀɮצW¨Ã¤À§O¦sÀÉ
¥Øªº¤u§@ªí¥´¶},­È¤Æ¨C­Ó¤u§@ªíÀYªº¦WºÙ¨Ãclear­ì¤½¦¡


*******************§Ú§Æ±æ¹F¨ìªº·s¼W¥[¥\¯à(¦³°ÝÃD,·|¤@ª½´`Àô¶K¸ê®Æ)
1. ¦Û°Ê°»´ú°Ó«~.xlsx¬O§_¤w¶}±Ò,¤w¶}«h©¿²¤,¥¼¶}«h¥´¶}
2. ¦Û°Ê°»´ú°Ó«~.xlsxªº¦C¼Æ(°Ó«~.xlsx·|¦³³Ì·sªº²£«~¸ê®Æ,¦Ó¥B¦C¼Æ·|¦³¼W¥[¤Î´î¤Öªº¥i¯à©Ê)
3. §â¸ê®Æªº"­È"(¤£­n®æ¦¡)¶K¨ì²z³f³æªº²Ä¤@­Ó¤u§@ªí"¥X³f¼Æ" (¦³¢±­Ó´ú¸ÕÀÉ¡G­¸¤ñ_·x·x.´ò¤f.xlsx¡þBF-QOO.xlsx,¥¿¦¡ªº§@·~ÁÙ·|¦³§ó¦hªºÀɮס^

¦]¬°§Ú¤£·|¼g³o¬qµ{¦¡,©Ò¥H¬O¥Î¤â°Êªº°µªk¡G
¶iµ{¦¡¤¤­×§ï,«ü©wA19¿ï¨ú¤@¾ã¦C,¹w³]¬°½Æ»s¤@¦C,¦p­n§R°£¤@¦C,«h­n
³oªí¥Ü­n¥ýª¾¹D¥X³f¼Æ»P°Ó«~Ä檺¦C¼Æ¦³¦h¤Ö¤£¦P
µM«áµ{¦¡·|±NWorkbooks("°Ó«~.xlsx").Sheets("­¸¤ñ°Ó«~").Range("°Ó«~Äæ")ªº¸ê®Æ¦Û°Ê¶K¤W
¥Ø«e¹w³]¬O´¡¤J¤@¦C«á,¶K¤W°Ó«~¸ê®Æ
­Y¬O¸ê®Æ¤£»ÝÅÜ°Ê®É,­nµù¸Ñ±¼,´N¤£·|°õ¦æ

¥Î¼gªº¥i¯àµLªk«Ü¸Ô²Ó,§Ú¤w§â¦³°ÝÃD³o¤@¬qµù¸Ñ¤F,¥ýrun¤Uµ{¦¡, ¥i¯à´N©ú¥Õ§Ú¦b»¡¤°»ò¡I
1.¤U­Ó¤ë²z³f³æ_´ú¸Õ.rar (226.82 KB)

¥»©«³Ì«á¥Ñ luhpro ©ó 2019-11-29 21:26 ½s¿è
Dear,
...
1. ¦Û°Ê°»´ú°Ó«~.xlsx¬O§_¤w¶}±Ò,¤w¶}«h©¿²¤,¥¼¶}«h¥´¶}...
PJChen µoªí©ó 2019-11-26 00:04

¥ý¸Ñ¨M§Aªº²Ä¤@­Ó°ÝÃD,
¦b§Aµoªº¨º¦ê : [µo°Ý] ¦Û°Ê®Mªí 5# ²Ä 5 ½g¸Ì,
§Úªº¦^¤å¸Ì¦³¤@¬q´N¬O¦b³B²z§A³o­Ó»Ý¨D :
  1. sPath = ThisWorkbook.Path ' ¦pªG­n«ü©w¥Ø¿ý, ¥u­n§ï¦¨¸Ó¥Ø¿ý§Y¥i, ¦p sPath = "D:"
  2.    sFlName = "°Ó«~.xlsx"
  3.    bMatch = False ' Àˬd '°Ó«~.xlsx' Àɮ׬O§_¤w¶}±Ò
  4.   For iI = 1 To Workbooks.Count
  5.      If Workbooks(iI).Name = sFlName Then
  6.        bMatch = True
  7.        Exit For
  8.      End If
  9.    Next iI
  10.    If bMatch Then
  11.      Set wsTar = Workbooks(sFlName).Sheets("­¸¤ñ°Ó«~")  ' Àɮפw¶}±Ò, ª½±µ¨ú¥Î
  12.      wsTar.Activate
  13.    Else
  14.      Set wsTar = Workbooks.Open(Filename:=sPath & "\" & sFlName).Sheets("­¸¤ñ°Ó«~")  ' Àɮש|¥¼¶}±Ò, ¶}±Ò«á¨ú¥Î
  15.    End If
  16.    With wsTar
  17.   ....
  18.   End With
½Æ»s¥N½X
¨ú¥Î®É¥u­n¦b With wsTar  »P End With ¨â¦æ¶¡¥Î . ¶}ÀYªº«ü¥O§Y¥i,
¨Ò¦p :    .[A5] = 16   ©Î   .Cells(3,5)=2    ...µ¥

2. ¦Û°Ê°»´ú°Ó«~.xlsxªº¦C¼Æ(°Ó«~.xlsx·|¦³³Ì·sªº²£«~¸ê®Æ,¦Ó¥B¦C¼Æ·|¦³¼W¥[¤Î´î¤Öªº¥i¯à©Ê)

³Ì¥½¦C¸¹¥i¥H¥ÎÃþ¦ü
  1.   lRow = .Cells(Rows.Count, 1).End(XlUp).Row    ' 1 ªí¨ú±o A Äæ³Ì¥½¦³¸ê®Æ¦C¸¹  
½Æ»s¥N½X
¨ú±o
¦³¨Æ¥ý¤U¤F,
¨ä¥¦ªº­Y§Ú¦³®É¶¡¤W¨Ó¦A¤¬¬Û°Q½×.

TOP

¦^´_ 2# luhpro
lRow = .Cells(Rows.Count, 1).End(XlUp).Row    ' 1 ªí¨ú±o A Äæ³Ì¥½¦³¸ê®Æ¦C¸¹
³o­ÓÀɧڬã¨s¤F«Ü¦h¤Ñ¤F,ÁÙ¬O¼g¤£¥X«á­±ªºµ{¦¡¡A¤£ª¾¹D¦p¦ó®M¥Î¨ì

2. ¦Û°Ê°»´ú°Ó«~.xlsxªº¦C¼Æ(°Ó«~.xlsx·|¦³³Ì·sªº²£«~¸ê®Æ,¦Ó¥B¦C¼Æ·|¦³¼W¥[¤Î´î¤Öªº¥i¯à©Ê)
¦]¬°¥¦°»´ú¨ì "­¸¤ñ_·x·x.´ò¤f.xlsx/ BF-QOO.xlsx"ªº¦C¼Æ«áÁÙ­n»P¨Ó·½ÀÉ °Ó«~.xlsx¤ñ¹ï,¦C¼Æ¬O¦hÁÙ¬O¤Ö?
¨Ì"°Ó«~.xlsx"ªº¦C¼Æ¬°·Ç,
¦hªº¦C¼Æ.....§â¦h¥X¨Óªº§R°£
¤Öªº¦C¼Æ.....§â¤£¨¬ªº¦C¼Æ·s¼W
³Ì«á§â"°Ó«~.xlsx" ªº³o¤TÄæ "®Æ¸¹        ¤J¼Æ        °Ó«~¦WºÙ"ªº­È(¤£­n®æ¦¡)
copy¨ì ¥Øªº¸ê®Æ§¨ªºÀÉ®×(¥Ø«e¬O "­¸¤ñ_·x·x.´ò¤f.xlsx/ BF-QOO.xlsx")

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2019-12-29 18:04 ½s¿è

Dear,
¤§«e·Q­nªº°µªk§¹¥þ°µ¤£¥X¨Ó,§Ú·Q±Nµ{¦¡³o­Ó¥\¯à³æ¿W©Ô¥X¨Ó,¨Ï¤§Â²¤Æ
¥HWorkbooks("°Ó«~.xlsx").Sheets("·s¤ë") A4:C©³ ¬°¸ê®Æ¨Ó·½
1) copy­È¨ì "1.¤U­Ó¤ë²z³f³æ_´ú¸Õ"¸ê®Æ§¨¤¤ªº¨C­ÓÀÉ®×Sheets("¥X³f¼Æ")ªºA3:C
2) ¦A¥HSheets("·s¤ë") A4:C©³§PÂ_¦C¼Æ
3) ·íSheets("¥X³f¼Æ")ªºA3:Cªº¦C¼Æ=Sheets("·s¤ë") A4:C «h¦sÀÉÃö³¬
4) ·íSheets("¥X³f¼Æ")ªºA3:Cªº¦C¼Æ>Sheets("·s¤ë") A4:C «h §R°£¦hªº¦C¼Æ ¦sÀÉÃö³¬
5) ·íSheets("¥X³f¼Æ")ªºA3:Cªº¦C¼Æ<Sheets("·s¤ë") A4:C «h §âDÄ椽¦¡©¹¤U½Æ¨î,¦p¦PSheets("·s¤ë") A4:C©³ ªº¦C¼Æ ¦sÀÉÃö³¬

²{¦b°µ²Ä¤@­Ócopy¸ê®Æªº¥\¯à,¤@ª½µLªk¥¿±`¶K¸ê®Æ,¥i§_À°¦£¬Ý¤U?? ¤U­Ó¤ë²z³f³æ_´ú.rar (264.71 KB)

TOP

¥»©«³Ì«á¥Ñ »a³· ©ó 2020-1-5 23:22 ½s¿è

¦^´_ 4# PJChen


    ¤p§Ì§Ú¸ÕµÛ§ï¤F¤@¤U¡A½ÐªA¥Î¡C

    ­ì¥»§A¼gªº§Ú¦³«O¯d¡AÅܦ¨µù¸Ñ±¡ªp¡A¥i¥H¸ò§Ú¼gªº°Ï¶ô¤ñ¹ï¤@¤U¡C

    ¦]¬°­n§ì°Ó«~ªºrow¼Æ¶q¡A©Ò¥H cells(rows.count,"C").end(xlup).row ¥²¶·¬O¥H...

    workbooks("°Ó«~.xlsx").WORKSHEETS("·s¤ë").cells(rows.count,"C").end(xlup).row¤U¥h¼g¡A¤~·|¥h§ì¨ì¸ÓSheetªºrow¼Æ¶q¡C

    «e´£¬Oworkbook °Ó«~¬O­n¶}±Òªºª¬ºA¡C Macro_T.zip (14.6 KB)

TOP

¦^´_ 5# »a³·

¥ýÁÂÁ§Aªºµ{¦¡,
´ú¸Õµ²ªG:µ{¦¡·|¤@ª½´`Àô,°±¤£¤U¨Ó,³Ì«á­n«öESC¥h²×¤î¥¦,¥i¥H«ç»ò­×¥¿©O?......¥t
4)~5) ªº§PÂ_»yªk,­þ¦ì¤j¤j±Ð¾Ç¤@¤U¶Ü?
1) copy­È¨ì "1.¤U­Ó¤ë²z³f³æ_´ú¸Õ"¸ê®Æ§¨¤¤ªº¨C­ÓÀÉ®×Sheets("¥X³f¼Æ")ªºA3:C
2) ¦A¥HSheets("·s¤ë") A4:C©³§PÂ_¦C¼Æ
3) ·íSheets("¥X³f¼Æ")ªºA3:Cªº¦C¼Æ=Sheets("·s¤ë") A4:C «h¦sÀÉÃö³¬

4) ·íSheets("¥X³f¼Æ")ªºA3:Cªº¦C¼Æ>Sheets("·s¤ë") A4:C «h §R°£¦hªº¦C¼Æ ¦sÀÉÃö³¬
5) ·íSheets("¥X³f¼Æ")ªºA3:Cªº¦C¼Æ<Sheets("·s¤ë") A4:C «h §âDÄ椽¦¡©¹¤U½Æ¨î,¦p¦PSheets("·s¤ë") A4:C©³ ªº¦C¼Æ ¦sÀÉÃö³¬

TOP

¦^´_ 5# »a³·

«á¨Ó§ï¦¨³o¼Ë´N¤£·|¤@ª½´`Àô¤F,²{¦b§Ú¥u³Ñ4~5ÁÙ¨S¦¨¥\....
  1.         Path = ".......\1.¤é¯Z²z³f´«ºâªí\"                 '¥t¦s¥Øªº¸ê®Æ§¨
  2.         File = Dir(Path & "*.xlsx")          '¨Ó·½ÀɦW
  3.             Do While File <> ""
  4.             Set mySheet = Workbooks("°Ó«~.xlsx").Worksheets("·s¤ë")
  5.             iRow = mySheet.Cells(Cells.Rows.Count, "C").End(xlUp).Row  '¬ÝCÄæ¦ì´Xµ§¸ê®Æ
  6. '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ¨Ó·½¸ê®Æ§¨ ²z³f³æ Sheets("¥X³f¼Æ")·s¼W.§R°£
  7.                 With Workbooks.Open(Path & File)
  8.                     ActiveWorkbook.Sheets("¥X³f¼Æ").Activate
  9.                     Range("A3:C" & iRow - 1).Value = mySheet.Range("A4:C" & iRow).Value  'iRow­pºâ¨Ó·½Àɪº¦C¼Æ,±qA4¶}©l,¥ØªºÀɱqA3¶}©l,·|¦h¶K¤@¦C,©Ò¥H¥ØªºÀÉ­n-1
  10.                     ActiveWorkbook.Close True   '¦sÀÉ«áÃö³¬ÀÉ®×
  11.                 End With
  12. '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  13.         File = Dir
  14.     Loop
  15. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# PJChen


    ·|´`Àô¶Ü...§Ú¦Û¤v¦brunªº®É­Ô¬O¨S°ÝÃD°ÕXD

TOP

´ú¸ÕÀÉ:
TEST001.rar (96.64 KB)

TOP

¦^´_ 9# ­ã´£³¡ªL

ÁÂÁ­ã¤j,
µy­×§ï¸ô®|,°õ¦æOK

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD