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

¤£¦P¬¡­¶Ã¯¸ê®Æ·J¾ã

¤£¦P¬¡­¶Ã¯¸ê®Æ·J¾ã

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-4-9 17:10 ½s¿è

½Ð±Ð¦U¦ì°ª¤â~§Ú·Q§âÀ£ÁYÀɸ̪ºS1¡BS2¤ÎS3ªº¤T­ÓÀɮ׸̪º³¡¤ÀÀx¦s®æ~½Æ»s¤U¨Ó¨Ã¶K¨ì¥t¥~¤@­Ó·sªºÀÉ®×(S-total)¡A³o¼ËªºVBA»yªk½d¥»¸Ó«ç»ò¼g¤~¦n~¨D°ª¤â«üÂI

problem.rar (22.65 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-4-9 17:11 ½s¿è

¦^´_ 1# mankind0703
À£ÁYÀɪº¬¡­¶Ã¯¬O¶}±Òªºµ{¦¡½X
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range, Wbo As Workbook
  4.     Set Rng = Workbooks("s-total.xlsx").Sheets("sheet1").[a1]   's-total.xlsx ¨S¸ê®Æ
  5.     'Set Rng = Workbooks("s-total.xlsx").Sheets("sheet1").[a1].End(xlDown).Offset(1)   's-total.xlsx ¸ê®Æ³sÄò©¹¤U½Æ»s
  6.     For Each Wbo In Workbooks
  7.         If Wbo.Name <> Rng.Parent.Parent.Name Then  'Rng¬OÀx¦s®æ :ªº¤÷¼h(¬°¤u§@ªí)ªº¤÷¼h(¬°¬¡­¶Ã¯)
  8.             Wbo.Sheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeConstants).CurrentRegion.Copy Rng
  9.             '½d³ò: Wbo:¦U¬¡­¶Ã¯.Sheets("¤u§@ªí").Range("AÄæ" ).SpecialCells(xlCellTypeConstants)(¦³¸ê®Æ).CurrentRegion(¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò)
  10.             '½d³ò.Copy(½Æ»s) (ªÅ¤@®æ)Rng(½d³ò)
  11.             Set Rng = Rng.End(xlDown).Offset(1)
  12.             'Rng ½d³ò.End(xlDown:"©¹¤U") ¨ì³Ì«á¦³¸ê®ÆªºÀx¦s®æ
  13.             '½d³ò.Offset (¦ì²¾¦C¼Æ,¦ì²¾Äæ¼Æ)  '±N½d³ò²¾°Ê¨ì«ü©w°Ï°ìªº½d³ò
  14.         End If
  15.     Next
  16. End Sub
½Æ»s¥N½X

TOP

·PÁÂGBKEE°ª¤â~¨º¦A½Ð±Ð±z¥t¥~¤@­Ó°ÝÃD~ À£ÁYÀɸ̪ºS1¡BS2¤ÎS3ªº¤T­ÓÀÉ®×~§Ú¥u·Q­n½Æ»s§Ú©Ò»Ý­nªºÀx¦s®æ(¥ÑTitle A¨ìTitleB)¶K¨ì¥t¥~¤@­Ó·sªºÀÉ®×(S-total)¡A~¦ý¬OÀx¦s®æx¬O§Ú¤£»Ý­n½Æ»sªº~³o¼Ë¸Ó«ç»ò¼g¤ñ¸û¦n©O?

problem-1.rar (25.29 KB)

TOP

¦^´_ 3# mankind0703
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range, Wbo As Workbook
  4.     Set Rng = Workbooks("s-total.xlsx").Sheets("sheet1").[a1]   's-total.xlsx ¨S¸ê®Æ
  5.     'Set Rng = Workbooks("s-total.xlsx").Sheets("sheet1").[a1].End(xlDown).Offset(1)   's-total.xlsx ¸ê®Æ³sÄò©¹¤U½Æ»s
  6.     For Each Wbo In Workbooks
  7.         If Wbo.Name <> Rng.Parent.Parent.Name Then  'Rng¬OÀx¦s®æ :ªº¤÷¼h(¬°¤u§@ªí)ªº¤÷¼h(¬°¬¡­¶Ã¯)
  8.             Wbo.Sheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeConstants).CurrentRegion.Copy Rng
  9.             '½d³ò: Wbo:¦U¬¡­¶Ã¯.Sheets("¤u§@ªí").Range("AÄæ" ).SpecialCells(xlCellTypeConstants)(¦³¸ê®Æ).CurrentRegion(¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò)
  10.             '½d³ò.Copy(½Æ»s) (ªÅ¤@®æ)Rng(½d³ò)
  11.             Set Rng = Rng.End(xlDown).Offset(1)
  12.             'Rng ½d³ò.End(xlDown:"©¹¤U") ¨ì³Ì«á¦³¸ê®ÆªºÀx¦s®æ
  13.             '½d³ò.Offset (¦ì²¾¦C¼Æ,¦ì²¾Äæ¼Æ)  '±N½d³ò²¾°Ê¨ì«ü©w°Ï°ìªº½d³ò
  14.         End If
  15.     Next
  16.      With Workbooks("s-total.xlsx").Sheets("sheet1").Cells
  17.             .Replace "x", "=100/1", xlWhole                 '½Æ»s«á±N:  x ¦r¦ê ´À´«¬°¤½¦¡
  18.             .SpecialCells(xlCellTypeFormulas).Delete        '§R°£¦³¤½¦¡ªºÀx¦s®æ
  19.         End With
  20. End Sub
½Æ»s¥N½X

TOP

·PÁÂGBKEE°ª¤â~

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD