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

[µo°Ý] ¦h±i¤u§@ªí¸ê®Æ¾ã¦X©óÁ`ªí

[µo°Ý] ¦h±i¤u§@ªí¸ê®Æ¾ã¦X©óÁ`ªí

·Q±NSUMMARY¥H¥~ªº¨C¤@±iWORKSHEET¤ºªºB4ªº¨C¤@­Ó½s¸¹, ¦bSUMMARYªºA2°_ÂШî30¦¸, ¤U¦C¼gªº¤Ó¤£¨¬¤F, ¥i¥H¨ó§U¶Ü? ª¦¤å«Ü¤[¤]·Q¤£³q:'(

Sub SummurizeSheets()
    Dim ws As Worksheet
    Dim myrange As Range
   
    Application.ScreenUpdating = False
    Sheets("Summary").Activate

    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            Sheets("summary").myrange("A2:A30").End(xlUp) = ws.Range("B4:B3").Copy
            ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
        End If
    Next ws
End Sub

VBA ¸ê®ÆÂà¸m4.rar (25.38 KB)

¦^´_ 1# missbb

¸Õ¸Õ¬Ý
        For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            ws.Range("B4").Copy
            aa = Sheets("summary").Range("A65536").End(xlUp).Row + 1
            Sheets("summary").Paste Sheets("summary").Range("A" & aa, "A" & aa + 29)
        End If
    Next ws

TOP

¦^´_ 2# lpk187

·s°ÝÃD, §Ú·Q¨C¦¸¦æ¾pCOPY®É, ­n¥ý±NSUMMARYªº¤º®eCLEAR, ¥[¤J¤U¦C, ¦ý¥X²{424 ªºERROR, ¬O¬Æ©²°ÝÃD©O?:dizzy:
Sub Clear_Contents_Range()
    Workbook.Sheets("summary").Cells.ClearContents
End Sub

TOP

¦^´_ 5# missbb

Workbook.Sheets("summary").Cells.ClearContents

¦¹³B»Ý­nª«¥ó (¿ù»~ 424)
­×§ï¬Ý¬Ý
  1. Workbooks("¬¡­¶Ã¯¦WºÙ").Sheets("summary").Cells.ClearContents
½Æ»s¥N½X
¥i¥H¤£¥ÎCopy
  1. For Each ws In Worksheets
  2.         If ws.Name <> "Summary" Then
  3.              Sheets("summary").Range("A65536").End(xlUp).Offset(1).Resize(30)= ws.Range("B4")
  4.         End If
  5.     Next
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE

CLEAR CONTENT¦¨¥\¤F, ¦ý¦]WORKBOOK¤º¦³¶W¹L1±iSHEET, ¨C±iSHEET§¡¥ÑB4°_¦³¤£¦Pªø«×ªº¼Æ­È, ²{¦b¬O¨ú¤F¨C¤u§@ªíªºB4¼Æ­È, §Ú§ï¤F¤U¦C, «o¤S¥u¨ú¤F¨C¤u§@ªíªº³Ì«á¤@¦C¼Æ­È, ½Ð«ü¾É?

Sheets("summary").Range("A65536").End(xlUp).Offset(1).Resize(30) = VBA.rar (26.49 KB)

TOP

¦^´_ 8# missbb


    ·í½Æ»sªº¨Ó·½¥u¦³¤@­ÓÀx¦s®æ¡A¤º¦s¥u·|§â³o­ÓÀx¦s®æªº¤º®e·í¦¨¤@­Ó¦r¦ê¡A©Ò¥H¥¦·|§â³o­Ó¦r¦ê¤À¦¨N­Ó¦r¦ê¤À§O½Æ»s¨ì¥ØªºªºÀx¦s®æ¡A
¦ý­Y¦³¶W¹L¤@­Ó¥H¤Wªº¨Ó·½Àx¦s®æ¡A«h·|¦¨¬°°}¦C¡A©Ò¥H¥²¶·¦¨¬°
     ¥ØªºÀx¦s®æªº½d³ò¼Æ¶q(Count)=¨Ó·½Àx¦s®æªº½d³ò¼Æ¶q(Count)
  1. Sub move()
  2. For Each ws In Worksheets
  3.     ll = ws.Name
  4.         If ws.Name <> "summary" Then
  5.             ½d³ò¼Æ¶q = ws.Range("B4", ws.Range("B65536").End(xlUp).Address).Count
  6.             ½d³ò°}¦C¤º®e = ws.Range("B4", ws.Range("B65536").End(xlUp).Address)'¨Ï¨ä¦¨¬°°}¦C
  7.             Sheets("summary").Range("A65536").End(xlUp).Offset(1).Resize(½d³ò¼Æ¶q) = ½d³ò°}¦C¤º®e
  8.         End If
  9.     Next
  10. End Sub
½Æ»s¥N½X

TOP

¦^´_ 9# lpk187

¹ï¤£°_, «ö§Aªº¤èªk¥u¯à±N¨C­Ó¤u§@ªíªºB4¤º®eÅã¥Ü1¦¸.


¦p§ï¬°RESIZE 30, «h¥X°t#NA



°ÝÃD¦b¨º©O?:dizzy:

TOP

¦^´_ 10# missbb

§Ú¦b9¼Ó´N¦³¸ÑÄÀµ¹§A¤F¡A³æ¤@¦³³æ¤@ªº°µªk¡A½d³ò¦³½d³òªº°µªk¡A³æ¤@¤£¤@©w¯à®M¥Î¦b½d³ò
    Sub move()
For Each ws In Worksheets
    ll = ws.Name
        If ws.Name <> "summary" Then
            ½d³ò¼Æ¶q = ws.Range("B4", ws.Range("B65536").End(xlUp).Address).Count
            ½d³ò°}¦C¤º®e = ws.Range("B4", ws.Range("B65536").End(xlUp).Address) '¨Ï¨ä¦¨¬°°}¦C
            For Each Rng In ½d³ò°}¦C¤º®e
                Sheets("summary").Range("A65536").End(xlUp).Offset(1).Resize(30) = Rng
            Next
        End If
    Next
End Sub

TOP

¦^´_ 11# lpk187

¦hÁ§A¤£Ãã³Ò­W¦aµ¹§Ú¸Ñµª, ÁöµM¾Ç²ß¤¤, ·|Ä~Äò§V¤O:L

TOP

¦^´_ 5# GBKEE

¦­«eµ¹§Úªº«ü¾É, ²{¨D±Ð¦p»Ý¨C­ÓPDF§¡µo¥X¤@­Ó¹q¶l, ¤U¦Cªºµ{¦¡­n¦p¦ó§ó§ï©O? ¨D½ç±Ð!

Sub ex()
Set d = CreateObject("Scripting.Dictionary")
With Worksheets("attendance report")
For Each a In .Range(.[E4], .[E4].End(xlDown))
  d(a.Value) = ""         '¨ú±o©Ò¦³¤£­«½Æ¤À©±
Next
F = InputBox("Enter your month")

For Each ky In d.keys
     .Range("B4").AutoFilter Field:=4, Criteria1:=ky
     If Dir("C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf") <> "" Then Kill "C:\Users\mariasfy\Desktop\" & ky & "_" & F & "201507.pdf" '¦P¦WÀɮקR°£
     .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False  '¥t¦s¦¨PDFÀÉ®×

Next

Dim Mail_Object, Mail_Single As Variant
Email_Subject = "ªù¥«¥X¶Ô³ø§i"
Email_Cc = ""
Email_Bcc = ""
Email_Body = "ªù¥«¥X¶Ô³ø§i, ½Ð¦^ÂÐ"

Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(o)

With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.CC = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.Attachments.Add ("C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf")
.Display
'.send
End With

If .FilterMode = True Then .ShowAllData 'Åã¥Ü©Ò¦³¸ê®Æ

End With

End Sub

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡µLªk¾B¾×¡j©È®É¶¡®ø³u¡Aªá¤F³\¦h¤ß¦å¡A·QºÉ¦U¦¡¤èªk­n¾B¾×®É¶¡¡Aµ²ªG¬O¡G®ö¶O¤F§ó¦h®É¶¡¡A¥B¤@µL©Ò¦¨¡I
ªð¦^¦Cªí ¤W¤@¥DÃD