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

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

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

½Ð°Ý§Ú¹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

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



    «Øij ¥i±N¹B§@¹Lµ{¶}±Ò
   µM«á ¤@¦¸¥Î1~2­ÓÀɮפU¥h´ú¸Õ ©Î ¥ÎF8 ¤@¨B¤@¨B¬Ý°ÝÃD©Ò¦b...

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

½Ð°Ý§Ú¹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.     xPath = Application.ActiveWorkbook.Path

  4.     Application.ScreenUpdating = False
  5.     Application.DisplayAlerts = False

  6.     For Each xWs In ThisWorkbook.Sheets
  7.         With Sheets(xWs.Name)
  8.             .UsedRange.Copy

  9.             With Workbooks.Add(1)
  10.                 .Sheets(1).Paste
  11.                 .SaveAs xPath & "\" & xWs.Name & ".xls", FileFormat:=xlExcel8
  12.                 .Close
  13.             End With
  14.         End With
  15.     Next
  16.     Application.DisplayAlerts = True
  17.     Application.ScreenUpdating = True
  18. End Sub
½Æ»s¥N½X

TOP

¤@¡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°Ú

TOP

¤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¨¥...

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

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

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¦^´_ 7# GBKEE

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

TOP

¦^´_  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
  1. Sub Splitbook2()     '  GBKEE
  2.     Dim xPath As String, xWs As Worksheet

  3.     xPath = Application.ActiveWorkbook.Path

  4.     Application.ScreenUpdating = False
  5.     Application.DisplayAlerts = False

  6.     For Each xWs In ThisWorkbook.Sheets
  7.         xWs.Copy                          '  ¤u§@ªí½Æ»s   ->   ¤@±i¤u§@ªíªº¬¡­¶Ã¯.  ->  ActiveWorkbook
  8.         
  9.         '  Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"     '«ü©w¸ô®|¦sÀÉ
  10.         Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls", FileFormat:=xlExcel8 '«ü©w¸ô®|¦sÀÉ
  11.         Application.ActiveWorkbook.Close True
  12.     Next
  13.    
  14.     Application.DisplayAlerts = True
  15.     Application.ScreenUpdating = True
  16. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD