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

[µo°Ý] ¦bVBA¤¤¡A¦p¦ó¨Ï¥Î¥¨¶°ÁôÂó¡¥÷¤u§@ªí¡B¤Î¨ú®øÁôÂÃ¥þ³¡¤u§@ªí¡H

[µo°Ý] ¦bVBA¤¤¡A¦p¦ó¨Ï¥Î¥¨¶°ÁôÂó¡¥÷¤u§@ªí¡B¤Î¨ú®øÁôÂÃ¥þ³¡¤u§@ªí¡H

½Ð°Ý¦U¦ì¥ý¶i¦bVBA¤¤¡A¦p¦ó¨Ï¥Î¥¨¶°ÁôÂó¡¥÷¤u§@ªí¡B¤Î¨ú®øÁôÂÃ¥þ³¡¤u§@ªí¡H
°²³]¦³15­Ó¤u§@ªí¡AA²Õ1.2.3¤u§@ªí¡BB²Õ4.5.6¤u§@ªí¡BC²Õ7.8.9¤u§@ªí¡BD²Õ10.11.12¤u§@ªí¡BE²Õ13.14.15
¥¨¶°1¡GA¡BB¤u§@ªí¤£ÁôÂáA¨ä¥LC¡BD¡BE¤u§@ªíÁôÂáC
¥¨¶°2¡GA¡BC¤u§@ªí¤£ÁôÂáA¨ä¥LB¡BD¡BE¤u§@ªíÁôÂáC
¥¨¶°3¡GA¡BD¤u§@ªí¤£ÁôÂáA¨ä¥LB¡BC¡BE¤u§@ªíÁôÂáC
¥¨¶°4¡GA¡BE¤u§@ªí¤£ÁôÂáA¨ä¥LB¡BC¡BD¤u§@ªíÁôÂáC

¦³§ä¨ìÁôÂÃ/¨ú®øÁôÂêºVBA¡A¦ý¤£ª¾¦p¦ó°õ¦æ³¡¥÷¡B¥þ³¡
Sub UnhideSheet()
       Sheets("Sheet1").Visible = True
End Sub

Sub HideSheet()
       Sheets("Sheet1").Visible = False
End Sub

½Ð¦U¦ì¥ý¶i¤£§[«ü±Ð¡CÁÂÁ¡C

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-20 10:23 ½s¿è

¦^´_ 1# goner


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P±¡¹Ò
«á¾ÇÂǦ¹ÃD¾Ç²ß¨ì«Ü¦hª¾ÃÑ,½m²ß¤F¦r¨å.¤@ºû°}¦C»PSelect Case,¾Ç²ßµ²ªG½Ð«e½ú¸Õ¸Õ¬Ý
¿é¤Jµ¡:


¿é¤Jµ¡¿é¤J 1 µ²ªG:


¿é¤Jµ¡¿é¤J 2µ²ªG:


¨Ì¦¹Ãþ±À¨ì 4
¿é¤J 0 «h¬°¥þ³¡¤u§@ªí³£Åã¥Ü

Option Explicit
Sub ¤u§@ªíÅã¥ÜÁôÂñ±¨î()
Application.ScreenUpdating = False
Dim N%, i&, xR, V, Y, Z, No, G1, G2, G3
No = InputBox("½Ð¿é¤J 0~4 ¼Æ¦r!", "¤u§@ªíÅã¥ÜÁôÂñ±¨î", 0)
If No < 0 Or No > 4 Then Exit Sub
Set Y = CreateObject("Scripting.Dictionary")
V = ",¤u§@ªí1,¤u§@ªí2,¤u§@ªí3,¤u§@ªí4,¤u§@ªí5,¤u§@ªí6,¤u§@ªí7,¤u§@ªí8,¤u§@ªí9,¤u§@ªí10,¤u§@ªí11,¤u§@ªí12,¤u§@ªí13,¤u§@ªí14,¤u§@ªí15"
V = Split(V, ",")
For Each xR In [A1:E1]
   Z = Split(xR.Address, "$")(1)
   Y(Z) = Array(V(1 + N), V(2 + N), V(3 + N))
   N = N + 3
Next
For Each xR In ActiveWorkbook.Worksheets
   xR.Visible = True
Next
If No = 0 Then GoTo 111
Select Case No
Case 1: G1 = Y("C"): G2 = Y("D"): G3 = Y("E")
Case 2: G1 = Y("B"): G2 = Y("D"): G3 = Y("E")
Case 3: G1 = Y("B"): G2 = Y("C"): G3 = Y("E")
Case 4: G1 = Y("B"): G2 = Y("C"): G3 = Y("D")
End Select
Sheets(G1).Visible = False
Sheets(G2).Visible = False
Sheets(G3).Visible = False

111
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Set Y = Nothing
Erase V
End Sub


'°õ¦æ¤U¦Cµ{¦¡½X¥i¨ú±o 15­Ó¤u§@ªí¦Wªº¦r¦ê
Sub ¦¬¶°15­Ó¤u§@ªí¦W()
Dim i&, Na$
For i = 1 To 15
   Na = Na & "," & Sheets(i).Name
Next
Workbooks.Add
[A1] = "V = """ & Na & """"
End Sub
'§â 15­Ó¤u§@ªí¦Wªº¦r¦ê »P¤W¤è¬õ¦r¸m´«±¼
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦pªG²Å¦X±¡¹Ò»Ý¨D:
1.¥i¥H½Õ´«¤u§@ªí¦ì¸m
2.¤£¥i§ó§ï¤u§@ªí¦WºÙ
3.¤u§@ªí¤£¥iµu¤Ö
4.¥i¥H¼W¥[·s¤u§@ªí

¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

·PÁÂAndy2483¥ý¶iªº¸Ñµª
¥H¤WVBAµ{¦¡½X°ò¥»¤W¥i¥H¨Ï¥Î¡A¦³±NV´À´«±¼¡A¿é¤J0¡B4¥¿±`¹B§@¡C
V = ",¤u§@ªí1,¤u§@ªí2,¤u§@ªí3,¤u§@ªí4,¤u§@ªí5,¤u§@ªí6,¤u§@ªí7,¤u§@ªí8,¤u§@ªí9,¤u§@ªí10,¤u§@ªí11,¤u§@ªí12,¤u§@ªí13,¤u§@ªí14,¤u§@ªí15"
°t¦X Sub ¦¬¶°15­Ó¤u§@ªí¦W() ´À´«¦¨
V = [A1]

¦ý¨Ï¥Î¤W¿é¤J1¡B2¡B3¬Ò·|¦b³o¦æ¥X²{¿ù»~ Sheets(G3).Visible = False ¡A¿ù»~´£¥Ü [°}¦C¯Á¤Þ¶W¥X½d³ò]¡A
¿ù»~«á E²Õ¤u§@ªí ¤£·|°õ¦æ¨ìÁôÂáC


'°õ¦æ¤U¦Cµ{¦¡½X¥i¨ú±o 15­Ó¤u§@ªí¦Wªº¦r¦ê
Sub ¦¬¶°15­Ó¤u§@ªí¦W()
Dim i&, Na$
For i = 1 To 15
   Na = Na & "," & Sheets(i).Name
Next
Workbooks.Add      <<´ú¸Õ®ÉµL¨Ï¥Î·s¼W¤u§@ªí¡A¦b­ì¬¡­¶Ã¯·s¼W [¤u§@ªí1] ¨Ï¥Î¹J¥H¤W°ÝÃD¡C
[A1] = "V = """ & Na & """"
End Sub
'§â 15­Ó¤u§@ªí¦Wªº¦r¦ê »P¤W¤è¬õ¦r¸m´«±¼

½Ð°Ý¦p¦ó½Õ¾ã¸Ñ¨M¡AÁÂÁ¡C:)

TOP

A²Õ³£¤£ÁôÂÃ, ¥N½X¤¤´N¤£¶·²z·|

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-20 16:48 ½s¿è

¦^´_ 4# goner


    °õ¦æ¥H¤Uµ{¦¡«á,§âµ²ªG½Æ»s¨ìVBA



Option Explicit
Sub TEST_20221220()
Dim A(100), i&, Na$, N%
For i = 1 To 15
   Na = Na & "," & Sheets(i).Name
Next
A(N) = "Sub ¤u§@ªíÅã¥ÜÁôÂñ±¨î()": N = N + 1
A(N) = "Application.ScreenUpdating = False": N = N + 1
A(N) = "Dim N%, i&, xR, V, Y, Z, No, G1, G2, G3": N = N + 1
A(N) = "No = InputBox(""½Ð¿é¤J 0~4 ¼Æ¦r!"", ""¤u§@ªíÅã¥ÜÁôÂñ±¨î"", 0)": N = N + 1
A(N) = "If No < 0 Or No > 4 Then Exit Sub": N = N + 1
A(N) = "Set Y = CreateObject(""Scripting.Dictionary"")": N = N + 1
A(N) = "V = """ & Na & """": N = N + 1
A(N) = "V = Split(V, "","")": N = N + 1
A(N) = "For Each xR In [A1:E1]": N = N + 1
A(N) = "   Z = Split(xR.Address, ""$"")(1)": N = N + 1
A(N) = "   Y(Z) = Array(V(1 + N), V(2 + N), V(3 + N))": N = N + 1
A(N) = "   N = N + 3": N = N + 1
A(N) = "Next": N = N + 1
A(N) = "For Each xR In ActiveWorkbook.Worksheets": N = N + 1
A(N) = "   xR.Visible = True": N = N + 1
A(N) = "Next": N = N + 1
A(N) = "If No = 0 Then GoTo 111": N = N + 1
A(N) = "Select Case No": N = N + 1
A(N) = "Case 1: G1 = Y(""C""): G2 = Y(""D""): G3 = Y(""E"")": N = N + 1
A(N) = "Case 2: G1 = Y(""B""): G2 = Y(""D""): G3 = Y(""E"")": N = N + 1
A(N) = "Case 3: G1 = Y(""B""): G2 = Y(""C""): G3 = Y(""E"")": N = N + 1
A(N) = "Case 4: G1 = Y(""B""): G2 = Y(""C""): G3 = Y(""D"")": N = N + 1
A(N) = "End Select": N = N + 1
A(N) = "Sheets(G1).Visible = False": N = N + 1
A(N) = "Sheets(G2).Visible = False": N = N + 1
A(N) = "Sheets(G3).Visible = False": N = N + 1
A(N) = "": N = N + 1
A(N) = "111": N = N + 1
A(N) = "ActiveWindow.ScrollWorkbookTabs Position:=xlFirst": N = N + 1
A(N) = "Set Y = Nothing": N = N + 1
A(N) = "Erase V": N = N + 1
A(N) = "End Sub" ': N = N + 1
Workbooks.Add
[A1].Resize(N + 1) = Application.Transpose(A())
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ goner ©ó 2022-12-20 16:55 ½s¿è

·PÁÂAndyªº¼ö¤ßÀ°¦£¥H¤Î¦U¦ìªº¦^ÂСA²{¦b§ä¨ì­ì¦]Åo^^
À˵ø [°Ï°ìÅܼÆ] µøµ¡¡Aµo²{¨ä¤¤¦³­ÓÅܼƦh¤F¤@­Ó "Âù¤Þ¸¹¡A¦Ó¾É­PµLªk¥¿½T§PÂ_¤u§@ªí¦WºÙ¡A½Õ¾ã«á¤w¥i¥¿±`¨Ï¥Î¥\¯à¡C
  1. Option Explicit
  2. Sub ¤u§@ªíÅã¥ÜÁôÂñ±¨î()
  3. Application.ScreenUpdating = False

  4. Dim N%, i&, xR, V, Y, Z, No, G1, G2, G3
  5. No = InputBox("½Ð¿é¤J 0~4 ¼Æ¦r!", "¤u§@ªíÅã¥ÜÁôÂñ±¨î", 0)

  6. If No < 0 Or No > 4 Then Exit Sub

  7. Set Y = CreateObject("Scripting.Dictionary")
  8. V = [A1]    ' ³o¸Ì°t¦X¥¨¶° ¦¬¶°15­Ó¤u§@ªí¦W() ¨Ï¥Î¡A»`¶°¥Ø«e¬¡­¶Ã¯©Ò¦³¤u§@ªí¦WºÙ¡C
  9. V = Split(V, ",")
  10. For Each xR In [A1:E1]
  11.    Z = Split(xR.Address, "$")(1)
  12.    Y(Z) = Array(V(1 + N), V(2 + N), V(3 + N))
  13.    N = N + 3
  14. Next

  15. For Each xR In ActiveWorkbook.Worksheets
  16.    xR.Visible = True
  17. Next

  18. If No = 0 Then GoTo 111:

  19. Select Case No
  20.     Case 1: G1 = Y("C"): G2 = Y("D"): G3 = Y("E")
  21.     Case 2: G1 = Y("B"): G2 = Y("D"): G3 = Y("E")
  22.     Case 3: G1 = Y("B"): G2 = Y("C"): G3 = Y("E")
  23.     Case 4: G1 = Y("B"): G2 = Y("C"): G3 = Y("D")
  24. End Select
  25. Sheets(G1).Visible = False
  26. Sheets(G2).Visible = False
  27. Sheets(G3).Visible = False

  28. 111:
  29. ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
  30. Set Y = Nothing
  31. Erase V
  32. End Sub

  33. '°õ¦æ¤U¦Cµ{¦¡½X¥i¨ú±o 15­Ó¤u§@ªí¦Wªº¦r¦ê
  34. Sub ¦¬¶°15­Ó¤u§@ªí¦W()

  35. Dim i&, Na$
  36. For i = 1 To 15
  37.    Na = Na & "," & Sheets(i).Name
  38. Next
  39. 'Workbooks.Add    ' ¥H­ì¦³¬¡­¶Ã¯¨Ï¥Î¡A©Ò¥H¤£·s¼W¬¡­¶Ã¯¡C
  40. [A1] = "V=""" & Na & ""    ' ³o¸Ì¦³°µ½Õ¾ã¡C­ì[A1] = "V = """ & Na & """"  ÅܼƷ|¦h¤F¤@­Ó "Âù¤Þ¸¹¡A¦Ó¾É­PµLªk¥¿½T§PÂ_¤u§@ªí¦WºÙ¡C

  41. End Sub
  42. '§â 15­Ó¤u§@ªí¦Wªº¦r¦ê »P¤W¤è¬õ¦r¸m´«±¼
½Æ»s¥N½X
¥H¤U¬O¤@¦¸ ¨ú®øÁôÂà ©Ò¦³¤u§@ªíµ{¦¡½X¡A¨Ñ°Ñ¦Ò¡C
  1. Sub UnhideAllSheets()
  2.         Dim UAS As Worksheet

  3.         For Each UAS In ActiveWorkbook.Worksheets
  4.                 UAS.Visible = xlSheetVisible
  5.         Next UAS

  6. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# Andy2483


²Ä¤@¦¸µo°Ý¡A¥H¤Wµo°Ý³£¨Sª`·N¨ì­n¨Ï¥Î [¦^ÂÐ] ¥\¯à¡A ½Ð¨£½Ì¡I
¦A¦¸ÁÂÁÂAndyªºÀ°¦£¡C
:handshake

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-21 08:41 ½s¿è

¦^´_ 1# goner


    ÁÂÁ«e½ú¦^´_»P¦Û¤v½m²ß½s¿è­×§ï
¤µ¤Ñ«á¾Ç¦A½Æ²ß³o©«µo²{³\¦h¦Ò¶q¤£¨¬,¦b¾Ç²ß¤F¤@¦¸,¥H¤U¬O¤ß±oµù¸Ñ,½Ð°Ñ¦Ò,½Ð¦U¦ì«e½ú­Ì¦b«ü¾É¨ä¥L»yªk,ÁÂÁÂ

Option Explicit
Sub ¤u§@ªíÅã¥ÜÁôÂñ±¨î()
Application.ScreenUpdating = False
'¡ô¿Ã¹õ¼È¤£¸òÀHµ{§ÇÅܤưõ¦æµ²ªG
Dim N%, No%, i&, V, xR, Y, G1, G2, G3
'¡ô«Å§iÅܼÆ:(xR ,V, Y, G1, G2, G3)¬O³q¥Î«¬ÅܼÆ
'(N,No)¬Oµu¾ã¼ÆÅܼÆ,i¬Oªø¾ã¼Æ

No = InputBox("½Ð¿é¤J 0~4 ¼Æ¦r!", "¤u§@ªíÅã¥ÜÁôÂñ±¨î", 1)
'¡ô¥ONo¬O InputBox ¨ç¦¡,1¬O¹w¥ý¸m¤Jªº¼Æ¦r
'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/inputbox-function

If No < 0 Or No > 4 Then Exit Sub
'¡ô¥O¦pªGNo³oµu¾ã¼Æ­È¤£¬O0,1,2,3,4¤§¤@!,´Nµ²§ôµ{¦¡°õ¦æ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY¬O ¦r¨å
V = Sheets(1).[A1]
'¡ô¥OV³o³q¥Î«¬ÅܼƬO ²Ä¤@­Ó¤u§@ªí[A1]Àx¦s®æ­È
V = Split(V, ",")
'¡ô¥OV³o³q¥Î«¬ÅܼÆÅܬ° ±N¦Û¤v³Q","²Å¸¹¤À³Îªº¤@ºû°}¦C
For Each xR In [{"A","B","C","D","E"}]
'¡ô³]¶¶°j°é!¥OxR³o³q¥Î«¬ÅܼƬO ¤@ºû°}¦C¸Ìªº¤@­Ó­È,±q«e­±½ü¨ì³Ì«á
   Y(xR) = Array(V(1 + N), V(2 + N), V(3 + N))
   '¡ô¥OxR°j°é­È¬Okey,Item¬O¤@ºû°}¦C,¸Ì­±¦³3­Ó­È,
   'N¬Oµu¾ã¼Æ,ªì©l­È¬O0,©Ò¥H¤@¶}©lªº3­Ó­È¬O[A1]Àx¦s®æ­È³Q¤À³Î«áªº¯Á¤Þ¸¹1,2,3­Ó°}¦C­È

   N = N + 3
   '¡ô¥ON²Ö¥[ 3
Next
For Each xR In ActiveWorkbook.Worksheets
'¡ô³]¶¶°j°é!¥OxR³o³q¥Î«¬ÅܼÆÅܬ°¦¹¤u§@¤¤¬¡­¶³¡ªº¤u§@ªí¤§¤@,±q«e­±½ü¨ì³Ì«á
   xR.Visible = True
   '¡ô¥OxR°j°é¤u§@ªíÅã¥Ü
Next
If No = 0 Then GoTo 111
'¡ô¦pªGNo³oµu¾ã¼Æ­È¬O 0,´N¸õ¨ì 111ªº¦ì¸mÄ~Äò°õ¦æ
Select Case No
'¡ô¨Ï¥Î Select Case ³¯­z¦¡,ÀHµÛNo³oµu¾ã¼Æ­È¹ï·Ó¦¹³¯­z¦¡¤º®e
'https://learn.microsoft.com/zh-tw/office/vba/language/concepts/getting-started/using-select-case-statements

Case 1: G1 = Y("C"): G2 = Y("D"): G3 = Y("E")
'¡ô¦pªGNo³oµu¾ã¼Æ­È¬O 1,´N¥OG1³o³q¥Î«¬ÅܼƬO ¥H"C"¦r¤¸¬d¦r¨åªº¨ìªº¤@ºû°}¦C,
'G2³o³q¥Î«¬ÅܼƬO ¥H"D"¦r¤¸¬d¦r¨åªº¨ìªº¤@ºû°}¦C,
'G3³o³q¥Î«¬ÅܼƬO ¥H"E"¦r¤¸¬d¦r¨åªº¨ìªº¤@ºû°}¦C

Case 2: G1 = Y("B"): G2 = Y("D"): G3 = Y("E")
'¡ôÃþ±À
Case 3: G1 = Y("B"): G2 = Y("C"): G3 = Y("E")
'¡ôÃþ±À
Case 4: G1 = Y("B"): G2 = Y("C"): G3 = Y("D")
'¡ôÃþ±À
End Select
Sheets(G1).Visible = False
'¡ô¥OG1³o³q¥Î«¬ÅܼƩұaªº°}¦C¦W¤u§@ªíÁôÂÃ
Sheets(G2).Visible = False
'¡ôÃþ±À
Sheets(G3).Visible = False
'¡ôÃþ±À

111
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'¡ô¤u§@ªí¯Á¤Þ¼ÐÅÒ²¾¨ì³Ì«e¤è
Application.Goto Sheets(1).[A1]
'¡ôÀx¦s®æ´å¼Ð¸õ¨ì²Ä¤@­Ó¤u§@ªí[A1]
Set Y = Nothing
Erase V
'¡ôÄÀ©ñÅܼÆ
End Sub

Sub UnhideAllSheets()
Dim UAS As Worksheet
For Each UAS In ActiveWorkbook.Worksheets
   UAS.Visible = xlSheetVisible
Next UAS
End Sub

'°õ¦æ¤U¦Cµ{¦¡½X¥i¨ú±o 15­Ó¤u§@ªí¦Wªº¦r¦ê
Sub ¦¬¶°15­Ó¤u§@ªí¦W()
Dim i&, Na$
For i = 1 To 15
   Na = Na & "," & Sheets(i).Name
Next
'Workbooks.Add
Sheets(1).[A1] = Na
'²Ä¤@­Ó¤u§@ªí[A1]±a¤J 15­Ó¤u§@ªí¦Wªº¦r¦ê
End Sub
'§â 15­Ó¤u§@ªí¦Wªº¦r¦ê »P¤W¤è¬õ¦r¸m´«±¼
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 9# Andy2483


    ÁÂÁÂÁÙ³o»ò¥Î¤ß¡B¸Ô²Óªºµù¸Ñ¡AÁÙ¦³ªþ¥[³sµ²ºô¯¸»¡©ú¡A·PÁ¡C
    ¦¹Åý¾Ç¥Í¾Ç²ß¤F«Ü¦h¡A§ó²M·¡ªº¤F¸Ñ¥Îªk¡C

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD