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

missbb

·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

missbb

¦^´_ 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

missbb

¦^´_ 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)

¤U¸ü¦¸¼Æ: 3

2015-8-2 15:13

missbb

¦^´_ 9# lpk187

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

¤U¸ü (5.4 KB)

2015-8-2 18:03

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

¤U¸ü (6.57 KB)

2015-8-2 18:03

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

missbb

¦^´_ 11# lpk187

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

missbb

¦^´_ 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

missbb

¦^´_ 11# GBKEE

Åý§Ú¸Õ¤@¤U, ·P¿E!:'(

missbb

¦^´_ 12# missbb

§Ú¥i¥H±N¤u§@ªíATTENDANCE REPORT V3:AZ3ªº¤é´ÁÂà¸m¨ì¤u§@ªíLEAVE SUMMAy, ¦ýv3:AZ3¦p¦ó¥Hcopy 30¦¸¹ïÀ³¨C­Ó­û¤u, ¦]­û¤u¤£­û1­Ó?

¦p¦ó¥i¥H±N¨C­Ó­û¤uªºATTENDANCE REPORT¤ºªº"°²´Á/¨Ò°²/³Æµù"¦C¤ºªº³Ò©Î¨Ò«ö­û¤u¤Î¤é´Á°t¸m©óleave summaryªºdÄæ©O? ¥Î¨ç¼Æ(IF(ISERROR(INDEX('ATTENDANCE REPORT'!$1:$1048576,MATCH($A$2,'ATTENDANCE REPORT'!$M:$M,0),MATCH(DAY($C2),'ATTENDANCE REPORT'!$3:$3,0))),"",INDEX('ATTENDANCE REPORT'!$1:$1048576,MATCH($A$2,'ATTENDANCE REPORT'!$M:$M,0),MATCH(DAY($C2),'ATTENDANCE REPORT'!$3:$3,0)))¬O¥i¥H°µ¨ì, ©Î¦p¦ó±N¨ç¼Æ¥[¤Jvba¤º?

¥¼§¹ªºvba:
Sheets("attendance report").Range("v3:az3").Copy
Sheets("leave summary").Range("c2").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True
   
·P¿E! Âà¸m¨ç¼Æ.rar (285.08 KB)

¤U¸ü¦¸¼Æ: 7

2015-8-8 23:23