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

[µo°Ý] ª«¥ó¬Ù²¤ªº¥["."»P¤£¥[°ÝÃD

[µo°Ý] ª«¥ó¬Ù²¤ªº¥["."»P¤£¥[°ÝÃD

¥»©«³Ì«á¥Ñ thps51134 ©ó 2011-12-7 09:26 ½s¿è

·Q½Ð±Ð§ÚªºÆ[©À­þ¸Ì·d¿ù¤F

¦bWith...End With ¤¤, ±NCells(), Range()µ¥«e­±¥[¤W"."®É
µo²{¥X²{"¿ù»~438...¤£¤ä´©¦¹ÄݩʩΤèªk"©Î"¿ù»~1004, class range select¥¢±Ñ"µ¥ªº¿ù»~°T®§

¬O Workbook.addªºÃö«Y¶Ü?
=====
¥Ø«e¥i¥H¥¿½T°õ¦æªºª©¥»¡G
  1. With Workbooks.add
  2.     With Sheet1
  3.         Range("A1").select
  4.     End with
  5. End with
½Æ»s¥N½X
¦ý¤§«e¾Ç¨ì¤F
With...End With
¨ä¤¤°µ°Ê§@®É
«e­±ª«¥óÁö¥i¬Ù²¤, ¦ý»Ý¥[­Ó"."
©Ò¥H­ì¥»¬O³o¼Ë¡G
  1. With Workbooks.add
  2.     With .Sheet1
  3.         .Range("A1").select
  4.     End with
  5. End with
½Æ»s¥N½X
¡®³o¼Ë¼gªº¡G
  1. With Workbooks.add
  2.     With Sheet1
  3.         .Range("A1").select
  4.     End with
  5. End with
½Æ»s¥N½X
¦ý´N·|¥X²{¿ù»~°T®§

½Ð«üÂI§Ú­þ¸Ì·d¿ù¤F¡AÁÂÁ¤j®a¡I

¦^´_ 11# thps51134
Option Explicit ³¯­z¦¡  ¦b¼Ò²Õ¼h¦¸¤¤±j­¢¨C­Ó¦b¼Ò²ÕùتºÅܼƳ£¥²¶·©ú½Tªº«Å§i¡C
¦p¹Ï


°j°é°õ¦æ¨ì³o¸Ì®É¡A¦ü¥G³Ì«á¤@¦Cªº¸ê°TµLªk§ó·s¡H (¤]©È§Ú§ï¼g¿ù¤F)
µ{¦¡½X¬O¨S°ÝÃDªº­n¬Ý¬ÝÀɮפº®e

TOP

¥»©«³Ì«á¥Ñ thps51134 ©ó 2011-12-20 10:47 ½s¿è

¦^´_ 10# GBKEE

¡@·Q½Ð±Ð±zcode¤¤¨â¦Cªº·N¸q
1. ²Ä¤@¦C "Option Explicit"

2. ²Ä11¦C "NewSh.Cells(Rows.Count, "A").End(xlUp).Offset(1)"
¡@¡@°j°é°õ¦æ¨ì³o¸Ì®É¡A¦ü¥G³Ì«á¤@¦Cªº¸ê°TµLªk§ó·s¡H (¤]©È§Ú§ï¼g¿ù¤F)
¡@¡@¨C¦¸©ó·sÀɮ׶K¤W³£¬O¶K¦b¦P¤@¦CQ_Q
  1. '¦Û¤v§ï¼gªº°j°é¤º; ²Ä¤E¦C°õ¦æ¦³°ÝÃD'
  2. For i = 22 To RowEnd      'RowEnd¬°¾ã­ÓSheet(1)¤¤ªº³Ì«á¤@¦C'
  3.     If Cells(i, 3).Value = "BU5" Then
  4.         If NewSh.Cells(2, 1) = "" Then  '¦]¬°Cells(1,1)³£¬°ªÅ, ©Ò¥H´«¤F§O®æ
  5.             Range(Cells(i, 1), Cells(i + 1, 2)).EntireRow.Copy NewSh.Rows(1)
  6.                  '·sÀɮ׳̫á¤@¦C«á
  7.                  '¦]¬°«e­±¨S¬Ý¨ìnewbo©Ò¥H´«¦¨NewSh...¤£ª¾¹D¦³¨S¦³»~·|'
  8.         Else
  9.             Range(Cells(i, 1), Cells(i + 1, 3)).EntireRow.Copy NewSh.Cells(Rows.count, "A").End(xlUp).Offset(1) '·sÀɮ׳̫á¤@¦C
  10.         End If
  11.     End If
  12. Next i
½Æ»s¥N½X
ÁÂÁÂ!

TOP

¦^´_ 9# thps51134
  1. Option Explicit
  2. Sub Ex()
  3.     Dim NewSh As Worksheet
  4.     With Workbook("Test.xls").Sheets(1)
  5.         Set NewSh = Workbooks.Add.Sheets(1)   'Åܼƫü©w¬° ·s¼W¬¡­¶Ã¯ªºSheets(1)
  6.         For i = 1 To RowEnd                   'RowEnd¬°¾ã­ÓSheet(1)¤¤ªº³Ì«á¤@¦C'
  7.             If ±ø¥ó = True Then
  8.                 If NewSh.Cells(1) = "" Then   'A1=""
  9.                     Rows(i).Copy newbo.Rows(1) '·sÀɮ׳̫á¤@¦C«á
  10.                 Else
  11.                     Rows(i).Copy NewSh.Cells(Rows.Count, "A").End(xlUp).Offset(1)  '·sÀɮ׳̫á¤@¦C
  12.                 End If
  13.             End If
  14.         Next
  15.         NewSh.Parent.SaveAs "¸ê®Æ§¨\ÀɮצWºÙ"    '·s¼Wªº¬¡­¶Ã¯¦sÀÉ
  16.     End With
  17. End Sub
½Æ»s¥N½X

TOP

¦^´_ 8# GBKEE

·Q¥ÎVBA°µªº¨Æ¡G
¡@¡@ 1. ±NTest.xlsªí®æÀÉÀY½Æ»s¨ì·s¶}ªºÀÉ®×
¡@¡@2. ¦^¨ìÂÂÀÉ®×(Test.xls), ¹ïRow data§@³v¦C§PÂ_
¡@¡@¡@¡@²Å¦X=> Copy¸Órow¨ì ·sÀɮ׳̫á¤@¦C«á
¡@¡@¡@¡@¤£²Å=> ¨ì¤U¤@¦C§PÂ_
³oÃ䤣ª¾¹D¸Ó¦p¦ó¦b¨â­ÓWorkbook¤¤¤Á´«¤~¦n
=====

    ¨ä¹ê«Ü·Qª¾¹D¦p¦ó°µ§¹©Ò¦³copy°Ê§@¦A¦sÀÉ
¡@¥i¬O¤£ª¾¹D¸Ó¦p¦ó¼gFor loop¤¤ªº
        with "­è­è·s¼Wªºworkbook"¡@
    ¦pªGª½±µ with workbook.add©È¤@¦¸loop´N·s¶}¤@­Óworkbook ...@@

ÁÂÁÂ!

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-12-14 16:48 ½s¿è

¦^´_ 7# thps51134
.Range(.Cells(1, 1), .Cells(4, 72)).Copy A      -> .Range(½d³ò)½Æ»s(¥þ³¡)¨ìÅܼƪ«¥óA
­Y³]" .Range(.Cells(1, 1), .Cells(4, 72))" ¬°B-> B.Copy A
ÁÙ¬O»¡«Øij±j¨î¥ý´À¨Ï¥ÎªÌ±N·sadd ªº workbook¦sÀÉ,¦A±N¥¦¥s¥X¨Ó?
À³¬O°µ§¹COPY ¦A¦sÀÉ

TOP

¦^´_ 6# GBKEE

    ©êºp¥¼¤Î®É¦^ÂÐ
    ·PÁ¨â¦ì... (²Û·\±»­±)
¡@³ºµM²´ªá·í¿ù»~

·QÄ~Äò¦V½Ñ¦ì½Ð±Ð
1. ¦b²Ä¥|¦æ¤¤ªº" .Range(.Cells(1, 1), .Cells(4, 72)).Copy A "ªº·N¸q¬°¦ó?
  1. With Workbooks.Add
  2.       Set A = .Sheets(1).Cells(1, 1) '«ü©w·s¬¡­¶Ã¯ªº²Ä¤@­Ó¤u§@ªíA1
  3.        With Workbooks("Test.xls").Sheets(1)
  4.           .Range(.Cells(1, 1), .Cells(4, 72)).Copy A '½Æ»sTEST.XLSªº²Ä¤@±i¤u§@ªí³¡¤À½d³ò¨ì·s¬¡­¶Ã¯¤¤
  5.        End With
  6. End With
½Æ»s¥N½X
­Y³]" .Range(.Cells(1, 1), .Cells(4, 72))" ¬°B
B.Copy A ¬O "copy B to A"¶Ü?

2. ­Y·Q¥t¥~¦b"Test.xls"§@³v¦æ§PÂ_
¥Oµ²ªG= trueªº¸Ó¦Ccopy to ·s¶}ªºWorkbook
¦Û¤v¸£³U¸Ìªº·§©À¹³³o¼Ë...
  1. With Workbook("Test.xls").Sheets(1)
  2.     for i = 1 to RowEnd 'RowEnd¬°¾ã­ÓSheet(1)¤¤ªº³Ì«á¤@¦C'
  3.         If true then
  4.             Rows(i).copy
  5.             With  ­è­è·s¶}ªºworkbook
  6.                 '±N©óWorkbook("Test.xls").Sheets(1)¤¤copyªºRow
  7.                 'paste¦b New Workbookªº³Ì«á¤@¦C¤§«á
  8.             End With
  9.         End if
  10.     Next i
  11. End With
½Æ»s¥N½X
ÁÙ¬O»¡«Øij±j¨î¥ý´À¨Ï¥ÎªÌ±N·sadd ªº workbook¦sÀÉ
¦A±N¥¦¥s¥X¨Ó?

·PÁÂ!

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-12-7 16:35 ½s¿è

¦^´_ 5# thps51134
µøµ¡ Windows¨S¦³Sheets(1)¦³ActiveSheet
µøµ¡ With Windows ("test.xls").Sheets(1)   §ï¬° ->  ¬¡­¶Ã¯   With WorkBooks("test.xls").Sheets(1)

TOP

¥»©«³Ì«á¥Ñ thps51134 ©ó 2011-12-7 15:23 ½s¿è

¦^´_ 4# Hsieh
  1. Sub NewMethod()
  2.     With Workbooks.Add
  3.       Set A = .Sheets(1).Cells(1, 1) '«ü©w·s¬¡­¶Ã¯ªº²Ä¤@­Ó¤u§@ªíA1
  4.        With Windows("test.xls").Sheets(1)
  5.           .Range(.Cells(1, 1), .Cells(4, 72)).Copy A '½Æ»sTEST.XLSªº²Ä¤@±i¤u§@ªí³¡¤À½d³ò¨ì·s¬¡­¶Ã¯¤¤
  6.        End With
  7.     End With
  8. End Sub
½Æ»s¥N½X
°»¿ù®Éhighlight»¡¦b²Ä4¦æµo²{°ÝÃD
ÁÙ¬O»¡, ·|¤£·|§Ú§ï¨ì¤F¤°»ò³]©w?
===
¨ä¹ê¥Î.select¬O¦]¬°
¨Ï¥Î¿ý»s¥¨¶°®É¶]¥XªºCode ¬°»Ý­n¥Î¨ìActivateªº
§ï¨Ó§ï¥h´N¦¨¤F²{¦bªº¼Ë¤l

¯uªº«Ü·Q¬Ù±¼
Hsieh¤jµ¹¤F§Ú¤@­Ó«Ü´Îªº¤è¦V~

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-12-8 08:34 ½s¿è

¦^´_ 3# thps51134
§A¸Õ¹L¥i¥Hª½±µµ¹­È¬O¥i¥H°õ¦æªº
©Ò¥HÀ³¸Ó·|µo²{¬OSelect³y¦¨ªº¿ù»~   
¸ÕµÛ¤£­n¨Ï¥ÎSELECT¡A¤]¥iÁקKµL¿×ªº¿Ã¹õ§ó·s
    With Workbooks.Add
      Set A = .Sheets(1).Cells(1, 1) '«ü©w·s¬¡­¶Ã¯ªº²Ä¤@­Ó¤u§@ªíA1
       With Workbooks("Test.xls").Sheets(1)
          .Range(.Cells(1, 1), .Cells(4, 72)).Copy A '½Æ»sTEST.XLSªº²Ä¤@±i¤u§@ªí³¡¤À½d³ò¨ì·s¬¡­¶Ã¯¤¤
       End With
    End With
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD