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

thps51134

·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

Hsieh

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

thps51134

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

Hsieh

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

thps51134

¤U¸ü (18.35 KB)
error 438

2011-12-7 15:15

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

GBKEE

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

thps51134

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

GBKEE

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

thps51134

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

ÁÂÁÂ!

GBKEE

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