[µo°Ý] ¤u§@ªí¿ï¨ú VBA»yªk

PJChen

¦U¦ì¥ý¶i¦n,

§Ú¦b³\¦h½d¨Ò¤¤¬Ý¨ìªº,³£¬O±q²ÄN­Ó¤u§@ªí¶}©l°_ºâ¦Ü³Ì«á¤@­Ó,¤£ª¾¬O§_¦³¥H¤Uªº¤è¦¡¥i¥Îªº»yªk?
2ºØ¿ï¨úªºÃþ«¬ªº»yªk,­n¦p¦ó¼gªk?
1. ±qSheets("2")ªº¤u§@ªí¦WºÙ(¤£¬O±q²Ä2­Ó¤u§@ªí¶}©l¼Æ),¤@ª½¨ì³Ì«á¤@­Ó¤u§@ªí¿ï¨ú°_¨Ó
2. ±qSheets("2")¶}©l¦ÜSheet("Á`ªí")µ²§ô,¤§¶¡ªº°_¨´½d³ò¤u§@ªí¿ï¨ú°_¨Ó
µM«á±NRange(B2:C3)­È¤Æ(Åý¤½¦¡Åܦ¨­È)

GBKEE

¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub EX() '**1. ±qSheets("2")ªº¤u§@ªí¦WºÙ(¤£¬O±q²Ä2­Ó¤u§@ªí¶}©l¼Æ),¤@ª½¨ì³Ì«á¤@­Ó¤u§@ªí¿ï¨ú°_¨Ó
   
3.     Dim Sh As Worksheet, Ar, M As String
   
4.     For Each Sh In Sheets
   
5.         If Sh.Name = "2" Then
   
6.             M = Sh.Name
   
7.         ElseIf M <> "" Then
   
8.             M = M & "," & Sh.Name
   
9.         End If
   
10.         '**Range(B2:C3)­È¤Æ(Åý¤½¦¡Åܦ¨­È)
    
11.         If M <> "" Then Sh.Range("B2:C3") = Sh.Range("B2:C3").Value
    
12.     Next
    
13.     Ar = Split(M, ",")
    
14.     Sheets(Ar).Select
    
15. End Sub
    
16. 
    
17. Sub EX1() '**2. ±qSheets("2")¶}©l¦ÜSheet("Á`ªí")µ²§ô,¤§¶¡ªº°_¨´½d³ò¤u§@ªí¿ï¨ú°_¨Ó
    
18.     Dim Sh As Worksheet, Ar, M As String
    
19.     For Each Sh In Sheets
    
20.         If Sh.Name = "2" Then
    
21.             M = Sh.Name
    
22.         ElseIf M <> "" Then
    
23.             M = M & "," & Sh.Name
    
24.         End If
    
25.         '**Range(B2:C3)­È¤Æ(Åý¤½¦¡Åܦ¨­È)
    
26.         If M <> "" Then Sh.Range("B2:C3") = Sh.Range("B2:C3").Value
    
27.         If Sh.Name = "Á`ªí" Then Exit For
    
28.     Next
    
29.     Ar = Split(M, ",")
    
30.     Sheets(Ar).Select
    
31. End Sub

½Æ»s¥N½X

¦^´_ 1# PJChen

PJChen

¦^´_ 2# GBKEE

G¤j¦n,
¥Ø«e§Ú¥ý´ú¸Õ²Ä¤@­Óµ{¦¡,±q¤u§@ªí2¤@ª½¨ì³Ì«á¤@­Ó¤u§@ªíªº¿ï¨ú,¨Ã¥B¥[¤W¤@¨Ç§Ú»Ý­nªº¥\¯à:
1. ¤u§@ªí¥H¼Æ¦r©R¦Wªº¬°1~31,¦ý¨ä¤¤¤]¦³¥Î¤å¦r©R¦Wªº,§Ú»Ý­nÅýµ{¦¡¦Û°Ê§PŪ¼Æ¦r©R¦Wªº¤u§@ªí,·í¤ë¥÷¨S¦³31¤é(or ¨Ò¦p2¤ë¨S¦³29~31®É),¥i¥H¥ý±N¦h¾lªº¤u§@ªí§R°£(¦ý­n²¤¹L¤å¦r©R¦Wªº¤u§@ªí)
2. ¦A±N«ü©wªº½d³ò­È¤Æ
¦ý°õ¦æ®É¥X²{"°õ¦æ¶¥¬q¿ù»~13,«¬ºA¤£²Å¦X",½Ð°Ý§Ú»Ý­n­×§ï¤°»ò¦a¤è?

1. Sub ¿ï¨ú¤u§@ªí() '**1. ±qSheets("2")ªº¤u§@ªí¦WºÙ(¤£¬O±q²Ä2­Ó¤u§@ªí¶}©l¼Æ),¤@ª½¨ì³Ì«á¤@­Ó¤u§@ªí¿ï¨ú°_¨Ó
   
2.     Dim W As Workbook, xSh As Worksheet, Sh As Worksheet, Ar, M As String
   
3.     Set W = Workbooks("Bifido_¤K°ô _6¤ë.xlsx")
   
4.     Set xSh = W.Sheets("31")
   
5.     xSh.Delete
   
6.     For Each Sh In Sheets
   
7.         If Sh.Name = "2" Then
   
8.             M = Sh.Name
   
9.         ElseIf M <> "" Then
   
10.             M = M & "," & Sh.Name
    
11.         End If
    
12.         '**Range(B2:C3)­È¤Æ(Åý¤½¦¡Åܦ¨­È)
    
13.         If M <> "" Then Sh.Range("B2") = Sh.Range("B2").Value
    
14.     Next
    
15.     Ar = Split(M, ",")
    
16.     Sheets(Ar).Select
    
17. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 3# PJChen


¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Sh As Worksheet, xday(1 To 2) As Integer, Msg As Boolean, Ar
   
4.     xday(1) = 4            '**«ü©w¶}©l¤é
   
5.     xday(2) = Day(DateSerial(Year(Date), Month(Date) + 1, 0))  '**¤ë©³¤é
   
6.     Application.DisplayAlerts = False
   
7.     For Each Sh In Sheets
   
8.         Msg = True
   
9.         If IsNumeric(Sh.Name) And Val(Sh.Name) > xday(2) Then
   
10.             Msg = False
    
11.             Sh.Delete
    
12.         ElseIf IsNumeric(Sh.Name) And Val(Sh.Name) < xday(1) Then
    
13.             Msg = False
    
14.         End If
    
15.         If Msg Then Ar = Ar & "," & Sh.Name: Sh.[B2] = Sh.[B2].Value
    
16.     Next
    
17.     Ar = Split(Mid(Ar, 2), ",")
    
18.     Sheets(Ar).Select
    
19.     Application.DisplayAlerts = True
    
20. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 4# GBKEE

G¤j¦n,

µ{¦¡´ú¸Õµ²ªG:
1.¥i¥H±NB2­È¤Æ,¦ýµLªk±N¦h¾l¤u§@ªí§R°£
2. ¥t¥~,¨C¤ë·|´£«e°µ¤U­Ó¤ëªº¤u§@ªí(¨Ò¦p:²{¦b´N­n¹w°µ6¤ë¥÷),©Ò¥H§Ú­×§ï¥H¤U,¨Ã¦P®É´ú¸Õ,Month(6) + 1µL½×¦³µL¥[1,³£¦P¼Ëµ²ªG,µLªk±N¦h¾l¤u§@ªí§R°£
½Ð¦AÀ°¦£¬Ý¤Uµ{¦¡,·PÁÂ....
    xday(2) = Day(DateSerial(Year(Date), Month(6) + 1, 0))   '**«ü©w¤ë,¤ë©³¤é

µÜº¸´I_¤­ªÑ.rar (170.2 KB)

¤U¸ü¦¸¼Æ: 1

2019-5-27 18:26

PJChen

¦^´_ 5# PJChen


G¤j¦n,
§Ú±N¸ô®|ªº¸ê®Æ§¨+µ{¦¡ÀÉ ¤wªþ¤W ´ú¸Õ¥Î.rar (207.44 KB)

¤U¸ü¦¸¼Æ: 1

2019-5-28 15:45