marc5051
11-10-2011, 10:42 AM
Hi All...
I am trying to create a daily report from master tracker for different entity for the daily basis at EOD (End of the Day).
the master tracker contain 4 sheets.
Sheet 1: Summary
Sheet 2: Typing
Sheet 3: Taxes
Sheet 4: Report
However the i have macro to create daily report for the Typing and Taxes at EOD. like wise from 10/31/2011 to 10/31/2011. but whenever i click on the Binder Report. it generate the report for entire month w/ pending files and cancelled files too..
instead of that i need the report for completed file for the current date or the date i have entered in the Report Sheet.
Note: macro to create a report for Taxes is not working.
Option Explicit
Sub FTSReportTyping()
Dim LR As Long
Dim savePath As String
Dim saveName As String
savePath = "C:\2011\" 'include the final \ in this string
saveName = "FTS Report Typing" & Format(Date, "MM-DD-YY")
With Sheets("Typing")
.Rows("7:7").AutoFilter
.Rows("7:7").AutoFilter Field:=3, Criteria1:=">" & Sheets("Report").[D3] & " 0:00", _
Operator:=xlAnd, Criteria2:="<" & Sheets("Report").[F3] & " 23:59"
LR = .Range("C" & .Rows.Count).End(xlUp).Row
If LR > 7 Then
.Range("A7:D" & LR & ",F7:F" & LR & ",L7:L" & LR).Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Columns.AutoFit
ActiveWorkbook.SaveAs Filename:=(savePath & saveName), FileFormat:=xlNormal
ActiveWorkbook.Close
End If
.AutoFilterMode = False
End With
End Sub
thank you very much in advance to read my query
...Marco
I am trying to create a daily report from master tracker for different entity for the daily basis at EOD (End of the Day).
the master tracker contain 4 sheets.
Sheet 1: Summary
Sheet 2: Typing
Sheet 3: Taxes
Sheet 4: Report
However the i have macro to create daily report for the Typing and Taxes at EOD. like wise from 10/31/2011 to 10/31/2011. but whenever i click on the Binder Report. it generate the report for entire month w/ pending files and cancelled files too..
instead of that i need the report for completed file for the current date or the date i have entered in the Report Sheet.
Note: macro to create a report for Taxes is not working.
Option Explicit
Sub FTSReportTyping()
Dim LR As Long
Dim savePath As String
Dim saveName As String
savePath = "C:\2011\" 'include the final \ in this string
saveName = "FTS Report Typing" & Format(Date, "MM-DD-YY")
With Sheets("Typing")
.Rows("7:7").AutoFilter
.Rows("7:7").AutoFilter Field:=3, Criteria1:=">" & Sheets("Report").[D3] & " 0:00", _
Operator:=xlAnd, Criteria2:="<" & Sheets("Report").[F3] & " 23:59"
LR = .Range("C" & .Rows.Count).End(xlUp).Row
If LR > 7 Then
.Range("A7:D" & LR & ",F7:F" & LR & ",L7:L" & LR).Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Columns.AutoFit
ActiveWorkbook.SaveAs Filename:=(savePath & saveName), FileFormat:=xlNormal
ActiveWorkbook.Close
End If
.AutoFilterMode = False
End With
End Sub
thank you very much in advance to read my query
...Marco