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

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

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

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

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

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

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD