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

[µo°Ý] ±N¸ê®Æ¦Û°Ê½Æ»s¨ì¡A¥Ø«eÂI¿ïªº©Ò¦b­¶­±¨Ã¦Û°Ê¿ï¾Ü³Ì«á¤@¦æªÅ¥Õ³B¶K¤W

¦^´_ 30# GBKEE

·PÁÂGBKEEª©¥D­×¥¿«áªºª©¥»
³oª©¥»µ{¦¡½X¤ñ¸û²³æ¡A¦Ó¥B¦³µùĶ¤]¤ñ¸û¦nŪ¤F¡A¤£¹L¦³´XÂI¥i§_À°¦£­n­×¥¿

1.¦]AQ¦³®É­Ô·|¨S¦³³]©w­È¡A©Ò¥H¯à§_¥u§PÂ_A¦C¦³´X¦æ¡AµM«á½Æ»sA5:AQ(A¦C³Ì«á¤@¦æ)
2.¥uÀx¦sÃö³¬"¥þ¦~«×¸ê®Æ®w"
3.¥u¶K¤W"­È"ªº³¡¤À´N¦n
¬O§_§â
  1. Rng.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
½Æ»s¥N½X
­×¥¿¦¨
  1.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  2.         :=False, Transpose:=False
½Æ»s¥N½X

TOP

¦^´_ 31# jackson7015
  1. Option Explicit
  2. Sub Ex()
  3.     Dim E As Variant, wbSh(1 To 2) As Worksheet, bNFind(1 To 2) As Boolean
  4.     Dim AR(1 To 2), xPath As String, Rng As Range
  5.     AR(1) = "¥þ¦~«×¸ê®Æ®w.xls"        'ÀɮצWºÙ
  6.     AR(2) = "·í¤ë³øªí.xls"            'ÀɮצWºÙ
  7.     xPath = "D:\"                     'Àɮתº¸ô®|
  8.     For Each E In Workbooks           '©Ò¦³¶}±Òªº¬¡­¶Ã¯ª«¥ó¶°¦X
  9.         If E.Name = AR(1) Then bNFind(1) = True  '¥þ¦~«×¸ê®Æ®w ¤w¶}±Ò
  10.         If E.Name = AR(2) Then bNFind(2) = True  '·í¤ë³øªí ¤w¶}±Ò
  11.     Next
  12.     For E = 1 To UBound(bNFind)
  13.         If Not bNFind(E) Then              'ÀÉ®×¥¼¶}±Ò
  14.             Workbooks.Open (xPath & AR(E))
  15.         End If
  16.         Set wbSh(E) = Workbooks(AR(E)).Sheets("ºî¦X¸ê®Æ®w")
  17.     Next
  18.     With wbSh(2) '·í¤ë³øªí"¤¤ªº[ºî¦X¸ê®Æ®w]
  19.         '***1.¦]AQ¦³®É­Ô·|¨S¦³³]©w­È¡A©Ò¥H¯à§_¥u§PÂ_A¦C¦³´X¦æ¡AµM«á½Æ»sA5:AQ(A¦C³Ì«á¤@¦æ)
  20.         'Set Rng = .Cells(.Rows.Count, .[AQ1].Column).End(xlUp) ' §ä¥½¦C
  21.         Set Rng = .Cells(.Rows.Count, "AQ").End(xlUp) ' §ä¥½¦C
  22.         If .[A5].End(xlDown).Row > Rng.Row Then
  23.             Set Rng = .Range(.[A5], .Range("AQ" & .[A5].End(xlDown).Row))
  24.         Else
  25.             Set Rng = .Range(.[A5], Rng)
  26.         End If
  27.     End With
  28.     With wbSh(1) '¥þ¦~«×¸ê®Æ®w"¸ê®Æªíªº[ºî¦X¸ê®Æ®w]
  29.         Rng.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
  30.         '***3.¥u¶K¤W"­È"ªº³¡¤À´N¦n
  31.         .UsedRange = .UsedRange.Value
  32.     End With
  33.     '***2.¥uÀx¦sÃö³¬"¥þ¦~«×¸ê®Æ®w"
  34.     Workbooks(AR(1)).Close True   '¥þ¦~«×¸ê®Æ®w. Ãö³¬¥B¦sÀÉ
  35. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 32# GBKEE

·PÁÂGBKEEª©¥Dªº­×¥¿
    °Ê§@¤è­±³£OK¤F¡A¤£¹L¶K¤W­Èªº³¡¤À·|³sÀx¦s®æ¦¡¤]¶K¤W¥h¡A²Ö¿n¤U¨Óªº¶q·|¨ÏÀx¦s®æ¦¡³¡¥÷¼É¼W
¥»·Q¦Û¤v§ó¥¿¡A¤£¹L¤£©ú¥Õ¨º¤@¦æªº§@¥Î¨ì©³¬O«ç»ò¹B¦æªº
¥u¦n¦bÀµ½ÐªO¥DÀ°¦£¤F

­ì¥»¥H¬°¬O§ï¥¿
  1.         '***3.¥u¶K¤W"­È"ªº³¡¤À´N¦n
  2.         .UsedRange = .UsedRange.Value
½Æ»s¥N½X
¤£¹L¬d¸ß¤F¤@¤U¡A¦¹¥N½Xªº§@¥Î¦n¹³³£¬O¸ÑÄÀ¬°'¥h°£¤½¦¡¡A©Ò¥H¤£¤Ó©ú¥Õ«ç»ò§ó¥¿...

TOP

¦^´_ 32# GBKEE
¤£¦n·N«ä¡A·Q½Ð±ÐGBKEEª©¥D
³o±ø¦C¸ÕÁÙ¬O¤£·|­×§ï¡A¥i§_À°¦£¬Ý¬Ý
  1. Rng.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
½Æ»s¥N½X
¦³¥[¤J¹L
  1. .PasteSpecial Paste:=xlPasteValues
½Æ»s¥N½X
¤£¹L»yªk¿ù»~..

©êºp¡AVBªº¯à¤O¯uªº«Ü®t
¦A½Ð¬Ý¬Ý¯à§_¥u¶K¤W"­È"¡A¤£»Ý­n®æ¦¡±ø¥ó

TOP

¦^´_ 34# jackson7015
¤Ï¹L¨Ó§@
  1. With wbSh(1) '¥þ¦~«×¸ê®Æ®w"¸ê®Æªíªº[ºî¦X¸ê®Æ®w]
  2.         'Rng.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
  3.         '***3.¥u¶K¤W"­È"ªº³¡¤À´N¦n
  4.         .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count, Rng.Columns.Count) = Rng.Value
  5.         '½Æ»s½d³òªºÁ`¦C¼Æ Rng.Rows.Count
  6.         '½Æ»s½d³òªºÁ`Äæ¼Æ Rng.Columns.Count
  7.     End With
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 35# GBKEE

·PÁÂGBKEEª©¥DªºÀ°¦£
­ì¨Ó­n¨Ï¥Î¥t¤@ºØ¼gªk¨Ó©w¸q½d³ò
ÁÂÁ«e½ú­Ìªº«ü±Ð~

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD