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

½Ð°Ý¦p¦ó±N¦h±i¤u§@ªí¾ã¦X¦¨¦bÁ`ªí¡A ¤u§@ªíÁ`ªíªº¢ÏÄæ¬O¦U¤u§@ªíªº¦WºÙ

½Ð°Ý¦p¦ó±N¦h±i¤u§@ªí¾ã¦X¦¨¦bÁ`ªí¡A ¤u§@ªíÁ`ªíªº¢ÏÄæ¬O¦U¤u§@ªíªº¦WºÙ

½Ð°Ý¦p¦ó±N¦h±i¤u§@ªí¾ã¦X¦¨¦bÁ`ªí¡A
¤u§@ªíÁ`ªíªº¢ÏÄæ¬O¦U¤u§@ªíªº¦WºÙ
ÁÂÁÂ

¦h±i¤u§@ªí¾ã¦X¦bÁ`ªí.zip (17.64 KB)

joyce

²o¯A®æ¦¡¤Î¦X¨Ö®æ, ¥ÎVBA§a~~
Sub §ó·s()
Dim Sht As Worksheet, R&, xE As Range, CL, N%
Call ²M°£
CL = Array(36, 35) 'ÃC¦â
For Each Sht In Sheets
    If Sht.Name Like "#######-#*" = False Then GoTo 101 '§PÂ_¤u§@ªí¦WºÙ¬O§_²Å¦X "¦~¤ë¤é-§Ç¸¹"
    R = Sht.Cells(Rows.Count, "D").End(xlUp).Row - 5
    If R <= 0 Then GoTo 101
    Set xE = Sheets("Á`ªí").Cells(Rows.Count, 1).End(xlUp)(2)
    Sht.Range("A6:G6").Resize(R - 1).Copy xE(1, 2)
    Sht.Range("A6").Resize(R - 1).Copy xE
    xE.Resize(R - 1) = Sht.Name
    N = 1 - N
    xE.Resize(R - 1, 8).Interior.ColorIndex = CL(N) '¥HÃC¦â°Ï¤À¦U¤u§@ªí¸ê®Æ(­Y¥Î¤£¨ì¥i§R)
101: Next
End Sub

Sub ²M°£()
Sheets("Á`ªí").UsedRange.Offset(1, 0).EntireRow.Delete
End Sub

Xl0000070.rar (18.73 KB)

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

TOP

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


    ÁÂÁ¦^´_
½Ð°Ý¥t¤@®æ¦¡®M³oµ{¦¡½X¡A­n§ïªº³¡¥÷°£¤F½d³ò¤Î¤u§@ªí¦WºÙ¬O§_²Å¦X¤§¥~¡A¬O§_ÁÙ¦³¨ä¥L¦a¤è¡]¦]¹ï¥¨¶°¤£¼ô¡A¥u¦³§ï½d³ò¡A¨ä¥L³¡¥÷¤£ª¾¦p¦ó­×§ï¡^


Sub §ó·s()
Dim Sht As Worksheet, R&, xE As Range, CL, N%
Call ²M°£
CL = Array(36, 35) 'ÃC¦â
For Each Sht In Sheets
    If Sht.Name Like "#######-#*" = False Then GoTo 101 '§PÂ_¤u§@ªí¦WºÙ¬O§_²Å¦X "¦~¤ë¤é-§Ç¸¹"
    R = Sht.Cells(Rows.Count, "D").End(xlUp).Row - 5
    If R <= 0 Then GoTo 101
    Set xE = Sheets("Á`ªí").Cells(Rows.Count, 1).End(xlUp)(2)
    Sht.Range("A5:N5").Resize(R - 1).Copy xE(1, 2)
    Sht.Range("A5").Resize(R - 1).Copy xE
    xE.Resize(R - 1) = Sht.Name
    N = 1 - N
    xE.Resize(R - 1, 8).Interior.ColorIndex = CL(N) '¥HÃC¦â°Ï¤À¦U¤u§@ªí¸ê®Æ(­Y¥Î¤£¨ì¥i§R)
101: Next
End Sub

Sub ²M°£()
Sheets("Á`ªí").UsedRange.Offset(1, 0).EntireRow.Delete
End Sub

¥t¤@§Î¦¡¦hªí¦X¨ÖÁ`ªí.zip (22 Bytes)

joyce

TOP

        ÀR«ä¦Û¦b : ¤Ñ¤W³Ì¬ü¬O¬P¬P¡A¤H¥Í³Ì¬ü¬O·Å±¡¡C
ªð¦^¦Cªí ¤W¤@¥DÃD