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

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

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

Andy2483

¦^´_ 14# Emily


    ÁÂÁ«e½ú
111¸ò«á¾Ç«Ü¤[¤F,«pÁy¥Ö²Ä1,¿ù»~³Ì¦h²Ä1,°ÝÃD³Ì¦h²Ä1,½Ð¤£­n¶û±ó¥L
ÁÂÁ«e½ú´£¿ô«á¾Ç¾Ç²ß¦hÂI¼Ö½ì,¤]¥i¥H§ó¿ô¥Ø§ä¨ì¥Ø¼Ð
«á¾Ç¥Î³o©«Åwªï«e½ú,½Ð«e½ú¦h¦h«ü±Ð
http://forum.twbts.com/thread-23851-1-1.html
Hi

Andy2483

¦^´_ 13# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É
«á¾Ç¦b¦¹½d¨Ò¾Ç²ß¨ì«Ü¦hª¾ÃÑ,½d¨Ò¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É,ÁÂÁÂ

Sub TEST_A1()
Dim S(5), Nx, SS$, Sht As Worksheet, Y As Boolean
'¡ô«Å§iÅܼÆ:S¬O¤@ºû°}¦CS(0)~S(5),Nx¬O³q¥Î«¬ÅܼÆ,Sht¬O¤u§@ªíÅܼÆ,SS¬O¦r¦êÅܼÆ
'Y¬O¥¬ªLÅܼÆ
Nx = InputBox("½Ð¿é¤J 0~4 ¼Æ¦r!", "¤u§@ªíÅã¥ÜÁôÂñ±¨î", 0)
'¡ô¥ONx³o³q¥Î«¬ÅܼƬOInputBox() ¨ç¦¡¦^¶Ç­È
'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/inputbox-function
If InStr("/1/2/3/4/0/", Nx) < 2 Then Exit Sub
'¡ô¦pªGNx³q¥Î«¬ÅܼƭȥΠInStr()¨ç¦¡¦^¶Ç­È<2 ,´Nµ²§ôµ{¦¡°õ¦æ
S(0) = "/Á`ªí/ªí1/ªí2/ªí3/"
'¡ô¥OS°}¦C¯Á¤Þ¸¹0ªº¦r¦ê¬OÂù¤Þ¸¹¥]§¨ªº¤å¦r²Å¸¹
S(1) = "/Sheet4/Sheet5/Sheet6/"
'¡ôÃþ±À
S(2) = "/Sheet7/Sheet8/"
'¡ôÃþ±À
S(3) = "/Sheet9/Sheet10/Sheet11/Sheet12/"
'¡ôÃþ±À
S(4) = "/Sheet13/Sheet14/Sheet15/"
'¡ôÃþ±À
SS = S(0) & S(Nx):
'¡ô¥OSS³o¦r¦êÅܼƬO ¯Á¤Þ¸¹0ªºS°}¦C­È ³s±µ¯Á¤Þ¸¹¬O NxÅܼƪºS°}¦C­È
'¦pªG·Q·s¼W¥[¤u§@ªí³£¬O­nÅã¥Üªº,´N¦bS(0)¸Ì·Ó³W«h²K¥[
'¦pªG·Q·s¼W¥[¤u§@ªíÀH»Ý¨D¸s²ÕÅã¥Ü/ÁôÂêº,´N¦bS(1)~S(4)¸Ì·Ó³W«h²K¥[
For Each Sht In Sheets
'¡ô³]¶¶°j°é!¥OSht ¬O¤u§@ªí¤§¤@,±q«e­±ªí½ü¨ì«á­±ªí
    Y = False
    '¡ôY³o¥¬ªLÅܼƬO False
    If Nx = 0 Or InStr(SS, "/" & Sht.Name & "/") > 0 Then Y = True
    '¡ô¦pªGNxÅܼƬO 0,©Î¥Î InStr()¨ç¦¡¦^¶Ç­È¤j©ó 0 ??,
    'InStr(¦pªG¬Ù²¤¡A·|±q²Ä¤@­Ó¦r¤¸¦ì¸m¶}©l·j´M , SS¦r¦êÅܼÆ, Sht°j°é¤u§@ªí¦W«e«á¥]§¨"/"²Å¸¹)
    'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/instr-function
    'if±ø¥ó¦¨¥ß!´NÅýY¥¬ªLÅܼƬO True
    Sht.Visible = Y
    '¡ô¥OSht°j°é¤u§@ªºÅã¥Ü©ÎÁôÂÃÀH Y¥¬ªLÅܼưµÅܤÆ
   '¦pªG¿é¤J 0 ·|¥þ³¡Åã¥Ü!!
Next
End Sub

Andy2483

¦^´_ 13# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É§â¤u§@ªíªºÅã¥Ü©ÎÁôÂåΥ¬ªLÅܼƱ±¨î,¬Ý±oÀ´,¦ý¬O«á¾Ç»Ý­n§ó§V¤O¾Ç²ß¤~¦³¾÷·|À³¥Î,«á¾Ç¥H«á¹J¨ìª«¥óªº0/1Åܤƥi¥H¦^¨Ó¬Ý³o©«
¦A¦¸ÁÂÁ«e½ú

Andy2483

¦^´_ 19# lee88


    °ª¤â¶³¶°ªº½×¾Â
ÁÂÁ«e½ú,«á¾ÇÂǦ¹©«¾Ç²ß¨ì«Ü¦hª¾ÃÑ,¾Ç²ß¤ß±o¦p¤U,½Ð«e½ú¦A«ü¾É

Á`ªí_¤u§@ªí©ú²Ó:

¤U¸ü (52.53 KB)

2022-12-27 16:25

¤u§@ªí¼Ò²Õ:

¤U¸ü (104.67 KB)

2022-12-27 16:26

Module¼Ò²Õ:

¤U¸ü (181.94 KB)

2022-12-27 16:27

´ú¸Õµ²ªG:

¤U¸ü (74.08 KB)

2022-12-27 16:27

§ä¤£¨ì:

¤U¸ü (46.97 KB)

2022-12-27 16:29