­ã´£³¡ªL

1) ©w¸q¦WºÙ
    =GET.WORKBOOK(1)&T(NOW())  ¥[³o­Ó¥i¥H¤u§@ªí·s¼W©Î§R°£¦Ó¦Û°Ê§ó·s
2) C1¤½¦¡
   =IF(A1="","",TRIM(RIGHT(SUBSTITUTE(LOOKUP(1,0/(RIGHT(QM)=A1),QM),"]",REPT(" ",99)),99)))
3) C3¤½¦¡/¥k©Ô¤U©Ô
   =INDEX(INDIRECT("'"&$C$1&"'!B:F"),ROW($A1)+3,COLUMN(A$1))&""
4) B3¤½¦¡/¤U©Ô
  =IF(C3="","",(N(B2)+1)^(C3=C2))

5) 刦KVBA
Sub 刦K()
Dim R, xE As Range
R = Application.Match(9E+307, [B:B])
If IsError(R) Then Exit Sub
Set xE = [J1].Cells(Rows.Count, 1).End(xlUp)(2)
With Range("A3:G" & R)
     xE.Resize(.Rows.Count, .Columns.Count) = .Value
End With
End Sub

¥HBÄæ¼Æ¦r¬°MATCH½d³ò, ¥Î9E+307§ä¨ì³Ì«á¤@µ§


====================================

­ã´£³¡ªL

¦^´_ 9# yc1031

¥Î¤£¨ì¤½¦¡,
¥u­n¤u§@ªí¦WºÙ³Ì«á¬°"#"¥[­^¤å¦r, §Y¦Û°Ê³°Äò§ì¥X¦³®Ä½d³ò¶K¤J­È:

Sub ¨Ì±b¤á¶K¤J­È()
Dim Sht As Worksheet, T$, R&, xE As Range
[Åçºâ!I3:P6000].ClearContents
For Each Sht In Sheets
    T = Right(Replace(UCase(Sht.Name), "#", "$"), 2)
    If Not T Like "$[A-Z]" Then GoTo 101
    R = Sht.Cells(Rows.Count, 2).End(xlUp).Row - 3
    If R <= 0 Then GoTo 101
    Set xE = [Åçºâ!I1].Cells(Rows.Count, 1).End(xlUp)
    xE(2, 3).Resize(R, 6) = Sht.[A4].Resize(R, 7).Value
    xE(2, 1).Resize(R) = UCase(Sht.Name)
    xE(2, 2).Resize(R) = Right(T, 1)
101: Next
End Sub


'=====================================

­ã´£³¡ªL

¦^´_ 11# yc1031

Sub ¨Ì±b¤á¶K¤J­È()
Dim Sht As Worksheet, T$, R&, xE As Range
[Åçºâ!I3:P6000].ClearContents
For Each Sht In Sheets
    If INSTR(Sht.NAME ,"#")=0 Then GoTo 101
    R = Sht.Cells(Rows.Count, 2).End(xlUp).Row - 3
    If R <= 0 Then GoTo 101
    Set xE = [Åçºâ!I1].Cells(Rows.Count, 1).End(xlUp)
    xE(2, 3).Resize(R, 6) = Sht.[A4].Resize(R, 7).Value
    xE(2, 1).Resize(R) = Sht.Name
    xE(2, 2).Resize(R) = "#" & SPLIT(Sht.Name,"#")(1)
101: Next
End Sub