ªð¦^¦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

¦^´_ 1# thps51134


    ·í°õ¦æWorkbooks.add®É
§@¥Î¬¡­¶Ã¯´N¬O¸Ó·s¼W¬¡­¶Ã¯
©Ò¥HWith Workbooks.add°Ï¬q¤U
With .Sheets(1)·|«ü¦V¸Ó·s¼W¬¡­¶Ã¯¤¤ªº²Ä¤@­Ó¤u§@ªí
¦ý¬O­Y¤Ö¤F"."
With Sheets(1)«h¬O«ü¦Vµ{¦¡½X©Ò¦bªº¬¡­¶Ã¯¤¤ªº²Ä¤@­Ó¤u§@ªí
¨º»ò¦b°µselect°Ê§@®É¡A¦]¬°¨Ã«D©ó§@¥Î¤¤¬¡­¶Ã¯¤¤°õ¦æ©Ò¥H·|¥X¿ù
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

¦^´_ 2# Hsieh

    Dear Hsieh¤j, ¦Ñ¹ê»¡
¡@Åý§Ú¬Û·í§x´bªº¨ä¤¤¤@ÂI´N¦b©ó
¡@§ÚªºCode¬O¼g¦b¤U­±©Ò¼gªº"Test.xls"ªºModule¤¤
  1.     ¢åindows("Test.xls").Activate
  2.     Range(Cells(1, 1), Cells(4, 72)).Copy
  3.    
  4.     With Workbooks.Add
  5.         With Sheets1
  6.             Cells(1, 1).Select '¤]§ï¬°' Cells(1,1).value= "OH" '¹L, ½T»{¹L°Ê§@¯uªº¬O·|§@¥Î¦b¦¹'
  7.             ActiveSheet.Paste
  8.         End With
  9.     End With
½Æ»s¥N½X
³o¼Ë¼g¤U¨Ó¡A³ºµM¥i¥H°õ¦æ¥X
Åý¦btest ¤¤Copyªº¸ê®Æ©ó·s¶}ªºworkbook¤¤ªºSheet1¤¤¶K¤W  
(§Úªº¥Øªº´N¬O­ncopyÂÂÀɮפ¤ªº³¡¤À¸ê®Æ, paste©ó¶}ªºÀɮפ¤)

Hsieh¤jªº¦^ÂФ]Åý§ÚÅåı§Úªº¿ù»~°T®§°ÝÃD...
À³¸Ó»P»yªk¤£¼ô¤j¦³Ãö«Y= =a
¦]¬°±N
With Sheet1 §ï¬° With .Sheets(1)«á
¥["."ªº´N¶¶§Q°õ¦æ¤F!
·PÁÂ!

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

¥»©«³Ì«á¥Ñ 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

¥»©«³Ì«á¥Ñ 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

¦^´_ 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-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

¦^´_ 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

¦^´_ 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

        ÀR«ä¦Û¦b : µoµÊ®ð¬Oµu¼ÈªºµoºÆ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD