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

EXCELªº¥Ø¿ý

EXCELªº¥Ø¿ý

Book1(4).zip (60.98 KB) ¦P¤@­ÓÀɤº¦³«Ü¦h«Ü¦hªº¤u§@ªí,«ç¼Ë¤~¯à±N³o¨Ç¤u§@ªíªº¦WºÙ,·J¾ã¦b"¥Ø¿ý"³o­Ó¤u§@ªíªºAÄæ?

¦^´_ 1# VANESSA

VBA
  1. Sub Sortsheets()
  2.     Dim S%, C%, SH%, AR()
  3.     SH = Worksheets.Count - 1
  4.     ReDim AR(1 To SH)
  5.     C = 1
  6.     For S = 1 To Worksheets.Count
  7.       If Sheets(S).Name <> "¥Ø¿ý" Then
  8.          AR(C) = Sheets(S).Name
  9.          C = C + 1
  10.       End If
  11.     Next S
  12.     Sheets("¥Ø¿ý").[A:A] = ""
  13.     Sheets("¥Ø¿ý").[A1].Resize(SH, 1) = Application.Transpose(AR)
  14. End Sub
½Æ»s¥N½X

TOP

¹ï¤£°_,§Ú¤£ª¾¹D³o­ÓVBA­n«ç»òCOPY¨ìÀɮפº,¬O­n©ñ¨ì¥¨¶°¶Ü?
¦A«ö°õ¦æ¥¨¶°¶Ü?¥i¬O¨S¦¨¥\吔,¥i¥H¦A±Ð¤@¤U¶Ü,ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-5-25 16:32 ½s¿è

¦^´_ 3# VANESSA
§AÀ³¸Ó¬O­n°µ¶W³sµ²§a¡A³o¼Ë¤]­nªð¦^¥Ø¿ýªº¾Þ§@
play.gif
  1. Private Sub Worksheet_Activate()
  2. Dim A As Range, Sh As Worksheet
  3. Columns("A:A").Clear
  4. r = 1
  5. For Each Sh In Sheets
  6.    If Sh.Name <> Me.Name Then
  7.       Set A = Cells(r, 1)
  8.            Me.Hyperlinks.Add Anchor:=A, Address:="", SubAddress:= _
  9.         "'" & Sh.Name & "'!A1", TextToDisplay:=Sh.Name
  10.        Sh.Hyperlinks.Add Anchor:=Sh.[A1], Address:="", SubAddress:= _
  11.         "'¥Ø¿ý'!A1", TextToDisplay:="ªð¦^¥Ø¿ý"
  12.        r = r + 1
  13.    End If
  14. Next
  15. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦n©Ç,¬°¤°»ò§Ú«ç»ò¸Õ³£¤£¦æ
1.¦b¤u§@ªí"¥Ø¿ý"«ö¥kÁä,¿ï¨ú©Ò¦³¤u§@ªí
2.¿ï"À˵øµ{¦¡½X'¶K¤Wµ{¦¡
3«ö'Ãö³¬'¦^¨ì¤u§@ªí
4.µ¥...µ¥...µ¥¦n¤[³£¨S¥X¨Ó,½Ð°Ý§Ú¨ì©³¬O­þ°µ¿ù¤F

TOP

·PÁ¤À¨É,
VBA ªGµM¬O³Õ¤jºë²`°Ú~~

TOP

¦^´_ 5# VANESSA


    ¦n©Ç,¬°¤°»ò§Ú«ç»ò¸Õ³£¤£¦æ
1.¦b¤u§@ªí"¥Ø¿ý"«ö¥kÁä,¿ï¨ú©Ò¦³¤u§@ªí
¤£»Ý­n¿ï¨ú©Ò¦³¤u§@ªí
¤u§@ªí"¥Ø¿ý"«ö¥kÁä¡A¿ï"À˵øµ{¦¡½X'¶K¤Wµ{¦¡
µM«á¦^¨ì¤u§@ªí«á¤Á´«¤u§@ªí¸Õ¸Õ¬Ý
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Åã¥Ü"»yĶ¿ù»~",§Ú§âµe­±¶K¦bªþÀÉ Doc1.zip (57.37 KB) ,Àµ½Ðª©¥DÀ°¦£

TOP

«Ü¦n¥Îªº¥N½X
§Ú¦Û¤v±NA1­×§ï¦¨¦p¤U­±¥N½X
®Ú¾Ú«á­±¸ê®Æªº¦ì¸m¥h§PÂ_
¥i¯à¨C­Ó¤Hªº¸ê®Æ¤£¤@¼Ë
¦A¦Û¦æ­×§ï
" & Sh.Range("iv3").End(xlToLeft).Offset(, 1).Address & "
  1. Dim A As Range, Sh As Worksheet
  2. Columns("A:A").Clear
  3. r = 1
  4. For Each Sh In Sheets
  5.    If Sh.Name <> Me.Name Then
  6.       Set A = Cells(r, 1)
  7.            Me.Hyperlinks.Add Anchor:=A, Address:="", SubAddress:= _
  8.         "'" & Sh.Name & "'!" & Sh.Range("iv3").End(xlToLeft).Offset(, 1).Address & "", TextToDisplay:=Sh.Name
  9.        Sh.Hyperlinks.Add Anchor:=Sh.Range("iv3").End(xlToLeft).Offset(, 1), Address:="", SubAddress:= _
  10.         "'¥Ø¿ý'!A1", TextToDisplay:="ªð¦^¥Ø¿ý"
  11.        r = r + 1
  12.    End If
  13. Next
½Æ»s¥N½X
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦pªG¨C­Ó¤u§@ªíA:AÄæ¦ì³£¦³¸ê®Æªº®É­Ô©O,³o¼Ë¤l­n¹ï¨C­Ó¤u§@ªí´¡¤J¤@¾ãÄæ¦ì¥ý§a
¤K¶³

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD