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

goner

½Ð°Ý¦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

¦^´_ 1# goner


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

¤U¸ü (50.46 KB)

2022-12-20 09:57

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

¤U¸ü (35.16 KB)

2022-12-20 09:58

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

¤U¸ü (36.03 KB)

2022-12-20 09:59

¨Ì¦¹Ãþ±À¨ì 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´«±¼

Andy2483

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

¤U¸ü (14.32 KB)

2022-12-20 10:39

goner

·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¸ü (11.36 KB)

2022-12-20 14:54

'°õ¦æ¤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:)

­ã´£³¡ªL

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

Andy2483

¦^´_ 4# goner


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

¤U¸ü (126.28 KB)

2022-12-20 16:48

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

goner

·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. 
   
5. Dim N%, i&, xR, V, Y, Z, No, G1, G2, G3
   
6. No = InputBox("½Ð¿é¤J 0~4 ¼Æ¦r!", "¤u§@ªíÅã¥ÜÁôÂñ±¨î", 0)
   
7. 
   
8. If No < 0 Or No > 4 Then Exit Sub
   
9. 
   
10. Set Y = CreateObject("Scripting.Dictionary")
    
11. V = [A1]    ' ³o¸Ì°t¦X¥¨¶° ¦¬¶°15­Ó¤u§@ªí¦W() ¨Ï¥Î¡A»`¶°¥Ø«e¬¡­¶Ã¯©Ò¦³¤u§@ªí¦WºÙ¡C
    
12. V = Split(V, ",")
    
13. For Each xR In [A1:E1]
    
14.    Z = Split(xR.Address, "$")(1)
    
15.    Y(Z) = Array(V(1 + N), V(2 + N), V(3 + N))
    
16.    N = N + 3
    
17. Next
    
18. 
    
19. For Each xR In ActiveWorkbook.Worksheets
    
20.    xR.Visible = True
    
21. Next
    
22. 
    
23. If No = 0 Then GoTo 111:
    
24. 
    
25. Select Case No
    
26.     Case 1: G1 = Y("C"): G2 = Y("D"): G3 = Y("E")
    
27.     Case 2: G1 = Y("B"): G2 = Y("D"): G3 = Y("E")
    
28.     Case 3: G1 = Y("B"): G2 = Y("C"): G3 = Y("E")
    
29.     Case 4: G1 = Y("B"): G2 = Y("C"): G3 = Y("D")
    
30. End Select
    
31. Sheets(G1).Visible = False
    
32. Sheets(G2).Visible = False
    
33. Sheets(G3).Visible = False
    
34. 
    
35. 111:
    
36. ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    
37. Set Y = Nothing
    
38. Erase V
    
39. End Sub
    
40. 
    
41. '°õ¦æ¤U¦Cµ{¦¡½X¥i¨ú±o 15­Ó¤u§@ªí¦Wªº¦r¦ê
    
42. Sub ¦¬¶°15­Ó¤u§@ªí¦W()
    
43. 
    
44. Dim i&, Na$
    
45. For i = 1 To 15
    
46.    Na = Na & "," & Sheets(i).Name
    
47. Next
    
48. 'Workbooks.Add    ' ¥H­ì¦³¬¡­¶Ã¯¨Ï¥Î¡A©Ò¥H¤£·s¼W¬¡­¶Ã¯¡C
    
49. [A1] = "V=""" & Na & ""    ' ³o¸Ì¦³°µ½Õ¾ã¡C­ì[A1] = "V = """ & Na & """"  ÅܼƷ|¦h¤F¤@­Ó "Âù¤Þ¸¹¡A¦Ó¾É­PµLªk¥¿½T§PÂ_¤u§@ªí¦WºÙ¡C
    
50. 
    
51. End Sub
    
52. '§â 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. 
   
4.         For Each UAS In ActiveWorkbook.Worksheets
   
5.                 UAS.Visible = xlSheetVisible
   
6.         Next UAS
   
7. 
   
8. End Sub

½Æ»s¥N½X

goner

¦^´_ 6# Andy2483


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

Andy2483

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

goner

¦^´_ 9# Andy2483


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