edmondsforum

¤£¦n·N«ä¡A§Ñ¤F´£¤Î¤@¥ó¨Æ
´N¬O ³æ»ù¤ÀªRÁ`ªíªº Äæ¼e ¯à«O«ù¤£ÅܶÜ
¦]¬°§Ú­ì¥»´N³]©w¦n¤F

edmondsforum

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


    ·PÁ­ã¤j!!!  §Ö¬½·Ç­C!!
    ·Ç¤j¡A§Ú³oÃ䪺°ÝÃD¤]¬O¤@¼Ë¡A

   1.³æ»ù¤ÀªRÁ`ªíªº¶µ¦¸¡A¯à¨Ì·Ó¦U¤l¶µ³æ»ù¤ÀªR½s¸¹ ¤@ ¤G ¤T ¥| ¤­ ¨Ì·Ó±Æ§Ç½Æ»s¹L¨Ó¶Ü?
      §Ú¦³¸ÕµÛ§ï ±Æ¤ô·¾-³æ»ù¤ÀªR §â¥¦§ï¦¨ ¤­ ¡A¥¦ÁÙ¬O±Æ²Ä¤@­Ó¡C

   2.¯àÅý³æ»ù¤ÀªRÁ`ªí¥þ³¡³£Åܦ¨¶Â¦â¶Ü?  (ÁöµM¤â°Ê§ï´N¦nXD) §Úµo²{ ³æ¦ì ¨ºÃä³£·|Åܦ¨ ¬õ¦â

edmondsforum

¦^´_ 2# jcchiang

ÁÂÁÂJ¤j ¬Û·í¦¨¥\!!
³oÃ䦳¨â­Ó°ÝÃD·Q½Ð±Ð¡G
1.³æ»ù¤ÀªRÁ`ªíªº¶µ¦¸¡A¯à¨Ì·Ó¦U¦a¤l¶µ³æ»ù¤ÀªR½s¸¹ ¤@ ¤G ¤T ¥| ¤­ ¨Ì·Ó±Æ§Ç½Æ»s¹L¨Ó¶Ü?
   §Ú¦³¸ÕµÛ§ï ±Æ¤ô·¾-³æ»ù¤ÀªR §â¥¦§ï¦¨ ¤­ ¡A¥¦ÁÙ¬O±Æ²Ä¤@­Ó¡C

2.¯àÅý³æ»ù¤ÀªRÁ`ªí¥þ³¡³£Åܦ¨¶Â¦â¶Ü?  (ÁöµM¤â°Ê§ï´N¦nXD) §Úµo²{ ³æ¦ì ¨ºÃä³£·|Åܦ¨ ¬õ¦â

­ã´£³¡ªL

Sub TEST()
Dim xR As Range, xS As Worksheet, xU As Range
With Sheets("³æ»ù¤ÀªRÁ`ªí")
     .UsedRange.Offset(5, 0).EntireRow.Delete
     Set xR = .[B6]
End With
For Each xS In Sheets
    If Right(xS.Name, 4) <> "³æ»ù¤ÀªR" Then GoTo 101
    Set xU = Intersect(xS.[B:H], xS.UsedRange).Offset(1, 0)
    With xR.Resize(xU.Rows.Count, xU.Columns.Count)
         xU.Copy .Cells
         .Value = .Value
    End With
    Set xR = xR(xU.Rows.Count + 1)
101: Next
Range(xR(-xR.Row + 3, 1), xR(-1, 7)).Name = "Print_Area"
End Sub

²Ä¢µ¦æ¥H¤W¦Û¦æ³B²z¡ã¡ã
¡@
¡@
¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×

jcchiang

¦^´_ 1# edmondsforum

¸Õ¸Õ¬Ý¬O§_²Å¦X
Sub ex()
Dim sht As Object
Dim r%
r = 6
Sheets("³æ»ù¤ÀªRÁ`ªí").Cells.Clear
For Each sht In Worksheets
   If sht.Name Like "*³æ»ù¤ÀªR" Then
      With Sheets(sht.Name)
         .Range(.[b2], .Cells(.[c65535].End(3).Row, 8)).Copy Sheets("³æ»ù¤ÀªRÁ`ªí").Cells(r, 2)     
         r = r + .[c65535].End(3).Row
      End With
   End If
Next
With Sheets("³æ»ù¤ÀªRÁ`ªí")
   .Cells.Font.Name = "µØ±dÁõ®ÑÅéW5"
   .[b5].Value = "¶µ¦¸"
   .[b5].HorizontalAlignment = xlCenter
   With .Range("B2:H2")
      .Merge
      .Value = "·PÁ³»¶®a±Ú°Q½×ª©"
      .HorizontalAlignment = xlCenter
      .Font.Bold = True
      .Font.Size = 16
   End With
   With .Range("B3:H3")
      .Merge
      .Value = "³æ»ù¤ÀªRªí"
      .HorizontalAlignment = xlCenter
      .Font.Underline = xlUnderlineStyleSingle
      .Font.Size = 14
   End With
   .Range("c4:H4").Merge
   .[c4].Value = "¤uµ{¦WºÙ¡G³Â»¶®a±Ú°Q½×ª©"
   .Range("c5:H5").Merge
   .[c5].Value = "¤uµ{½s¸¹¡GExcelvba"
   .Range(.[b6], .Cells(.[c65535].End(3).Row, 8)) = .Range(.[b6], .Cells(.[c65535].End(3).Row, 8)).Value  
   .Columns("A:I").AutoFit
End With
End Sub