[µo°Ý] VBA¹BÂà°±¤£¤F

missbb

½Ð°Ý§Ú¹B¦æ³o­ÓVBA, ¦³30­ÓSHEETS, ¦ý¹BÂà¤F5¤ÀÄÁÁÙ¨S¦³°±¹y, ¬O¦³¬Æ»ò¿ù»~©O? ¤S¦p¦ó¥H¥H¦b§¹¦¨¦sÀÉ«á¦Û°ÊÃö³¬ÀÉ®×?
½Ð½ç±Ð!
Sub Splitbook()

Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"
    Application.ActiveWorkbook.Close True
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

missbb

¦^´_ 10# c_c_lai


    ¦hÁ«ü¾É:D

c_c_lai

¦^´_  missbb
§Aªºµ{¦¡¨S°ÝÃD.
¥i¯à¦]¦³30­ÓSHEETSªº¸ê®Æ¤j, ©Ò¥H·|¹BÂà¤F5¤ÀÄÁÁÙ¨S¦³°±¹y,¦ý­@¤ßµ¥­Ô·| ...
GBKEE µoªí©ó 2015-8-7 14:35

¦b§ÚªºExcel 2010 ´ú¸Õ®É¡A¦pªG¤£¥[¤J FileFormat:=xlExcel8 ªº¸Ü¡A
Àx¦sªº xls Àɮק¡µLªk¶}±Ò (¦pªþ¹Ï)¡A ©Ò¥H§Ú¤~·|µy¥[­×§ï¡AÁÂÁ±zªº«ü¾É¡I

¤U¸ü (14.62 KB)

2015-8-8 09:46

1. Sub Splitbook2()     '  GBKEE
   
2.     Dim xPath As String, xWs As Worksheet
   
3. 
   
4.     xPath = Application.ActiveWorkbook.Path
   
5. 
   
6.     Application.ScreenUpdating = False
   
7.     Application.DisplayAlerts = False
   
8. 
   
9.     For Each xWs In ThisWorkbook.Sheets
   
10.         xWs.Copy                          '  ¤u§@ªí½Æ»s   ->   ¤@±i¤u§@ªíªº¬¡­¶Ã¯.  ->  ActiveWorkbook
    
11.         
    
12.         '  Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"     '«ü©w¸ô®|¦sÀÉ
    
13.         Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls", FileFormat:=xlExcel8 '«ü©w¸ô®|¦sÀÉ
    
14.         Application.ActiveWorkbook.Close True
    
15.     Next
    
16.    
    
17.     Application.DisplayAlerts = True
    
18.     Application.ScreenUpdating = True
    
19. End Sub

½Æ»s¥N½X

missbb

¦^´_ 7# GBKEE

¦hÁÂ!·|¦A§V¤O!

mark15jill

¦^´_  missbb
§Aªºµ{¦¡¨S°ÝÃD.
¥i¯à¦]¦³30­ÓSHEETSªº¸ê®Æ¤j, ©Ò¥H·|¹BÂà¤F5¤ÀÄÁÁÙ¨S¦³°±¹y,¦ý­@¤ßµ¥­Ô·| ...
GBKEE µoªí©ó 2015-8-7 14:35

§Ñ°O ­ì©l¸ê®Æ¼Æ¶q¦h¹è¤]·|¼vÅT³t«×.... «¢

    ¨ü±Ð¤F~ ÁÂÁÂ

GBKEE

¦^´_ 1# missbb
§Aªºµ{¦¡¨S°ÝÃD.
¥i¯à¦]¦³30­ÓSHEETSªº¸ê®Æ¤j, ©Ò¥H·|¹BÂà¤F5¤ÀÄÁÁÙ¨S¦³°±¹y,¦ý­@¤ßµ¥­Ô·|§¹¦¨ªº.
¦^´_ 3# c_c_lai
¦^´_ 4# jackyq
¦^´_ 5# mark15jill

1. For Each xWs In ThisWorkbook.Sheets
   
2.     xWs.Copy  '¤u§@ªí½Æ»s  -> ¤@±i¤u§@ªíªº¬¡­¶Ã¯. -> ActiveWorkbook
   
3.     Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls" '«ü©w¸ô®|¦sÀÉ

½Æ»s¥N½X

missbb

¦U¦ì¤j¤j,
¦³³Ò, «Ü¶}§Ö°ÝÃD¸Ñ¨M¤F.

mark15jill

¤j¤j¦n
Copy ®É¦Û¤v·´·s¼W°Ú
jackyq µoªí©ó 2015-8-7 10:24

    copy ¥u¬O½Æ»s.. §A¨S¦³¥ô¦ó pasteªº«ü¥Oªü..

¥u¦³½Æ»s¨S¦³¶K¤W ­þ¨Óªº·s¼W¥i¨¥...

jackyq

¤@¡B  .Copy ½d³ò¤Ó©â¶H¤F¡AÀ³©ú½T§iª¾¨ä¹ê»Ú½Æ»s½d³ò¡F
¤G¡B ©pÀɮש|¥¼·s¼W¦ó¨ÓÀx¦sÀɮסH
¤T¡B ¥H©p ...
c_c_lai µoªí©ó 2015-8-7 10:18

¤j¤j¦n
Copy ®É¦Û¤v·´·s¼W°Ú

c_c_lai

½Ð°Ý§Ú¹B¦æ³o­ÓVBA, ¦³30­ÓSHEETS, ¦ý¹BÂà¤F5¤ÀÄÁÁÙ¨S¦³°±¹y, ¬O¦³¬Æ»ò¿ù»~©O? ¤S¦p¦ó¥H¥H¦b§¹¦¨¦sÀÉ«á¦Û°Ê ...
missbb µoªí©ó 2015-8-6 23:48

¤@¡B  .Copy ½d³ò¤Ó©â¶H¤F¡AÀ³©ú½T§iª¾¨ä¹ê»Ú½Æ»s½d³ò¡F
¤G¡B ©pÀɮש|¥¼·s¼W¦ó¨ÓÀx¦sÀɮסH
¤T¡B ¥H©p­ì¥»¤º®e¬°½Z©³¡Bµy¤©­×¥¿¡A½Ð©p¦Û¦æ¦A´ú¸Õ¬Ý¬Ý¡G

1. Sub Splitbook()
   
2.     Dim xPath As String, xWs As Worksheet
   
3. 
   
4.     xPath = Application.ActiveWorkbook.Path
   
5. 
   
6.     Application.ScreenUpdating = False
   
7.     Application.DisplayAlerts = False
   
8. 
   
9.     For Each xWs In ThisWorkbook.Sheets
   
10.         With Sheets(xWs.Name)
    
11.             .UsedRange.Copy
    
12. 
    
13.             With Workbooks.Add(1)
    
14.                 .Sheets(1).Paste
    
15.                 .SaveAs xPath & "\" & xWs.Name & ".xls", FileFormat:=xlExcel8
    
16.                 .Close
    
17.             End With
    
18.         End With
    
19.     Next
    
20.     Application.DisplayAlerts = True
    
21.     Application.ScreenUpdating = True
    
22. End Sub

½Æ»s¥N½X