Create Daily Report From Master Tracker.

Hello experts...

Good evening to all of you, and here i am looking for how Create Daily Report from Master Tracker at End of the Day. Hmm As of now i am having 3 major sheets for different Clients. At present i am doing it manually like coping all data and paste to other sheet, meantime it takes lot of time to create a report. Instead of that what i am looking that daily report will generate automatically with current date datum. I am having 3 major client as i mentioned above. Are there any possibility to create a daily report with current date data. i not good enough sounded in VBA or in programming. Please help with this if you excellent people can do it...

For your reference, please find the attached file.

I don't see your sample results.

I wasn't looking for a code. I just can't picture what you're asking help doing.

You've already got buttons with AutoFilter code in them, so you should have an idea now of how your code should look.

Even if you don't, turn on the macro recorder and let it record you doing your whole process one time to filter the sheets and save the data off. That code will get you over half the way there and give us somewhere further along to start.

Hello trucker10..

i am not looking for time Macro, Please read my query sir.

Marc

password is--------

A report based on dates in which column?

Originally Posted by JBeaucaire

A report based on dates in which column?

This is the info needed.

Thank you JB.,

for an example, In Option1 spread sheet i want these all things in new workbook, Sr. # , Order #, Received Date & Time, Completed Date & time, TAT And Processor(s) Name

See prior question.

This macro will do the OPTIONS1 for you. Just create the other versions for the other buttons and date range cells.

Option Explicit

Sub Button2_Click()
Dim LR       As Long
Dim savePath As String
Dim saveName As String

savePath = "C:\2010\"       'include the final \ in this string
saveName = "Options1 " & Format(Date, "MM-DD-YY")

With Sheets("Option1")
    .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 & ",H7:H" & LR & ",K7:K" & 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

Marco, you have to be able to handle the most basic of functions...seriously (stern voice).

1) Open the VBEditor
2) Insert a new empty module (Insert > Module)
3) Paste in the code (change the name of the macro to something like Options1Report
4) Close the VBEditor
5) Right-click your Option1 Report button and select ASSIGN MACRO
6) Choose the macro from the list and click OK.

Marco, you have to be able to handle the most basic of functions...seriously (stern voice continues...)

1) Open the VBEditor
2) Insert a new empty module (Insert > Module)
3) Paste in the code (change the name of the macro to something like Options1Report <<<<<<<<<<<<<<<<<<
4) Close the VBEditor
5) Right-click your Option1 Report button and select ASSIGN MACRO <<<<<<<<<<<<<<<<<<
6) Choose the macro from the list and click OK. <<<<<<<<<<<<<<<<<<


I've marked the things you DIDN'T do in the uploaded sheet. Button1_Click is a bad name for a macro, fix it. Then attach the macro to your button if you EVER expect it to work by clicking the button.

It also appears you haven't changed the savePath, so you'll need to look in "C:\2010\" for your new files.

And the uploaded file has settings 6/14-6/14 with no matching data...so no workbooks would be created.

Yes JB,

i have changed it, but still it cant generate the report. Let me know is code is perfect or not.

Marc

When I ran the exact report you uploaded in post #35 for dates 6/1-6/14, i got this, no May 28.

Looks like it's good to go.
=============

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

You do need to fully edit the macro for each version you create. The OPTIONS1 macro is looking at cells D3 and F3 for the dates. The next version for FNAS would need to search D4 and F4, right? You edited that, too, yes?

    .Rows("7:7").AutoFilter Field:=3, Criteria1:=">" & Sheets("Report").[D3] & " 0:00", _
                     Operator:=xlAnd, Criteria2:="<" & Sheets("Report").[F3] & " 23:59"

Let's see your code. When I run it, I only get two lines of 6/4/2010.

Originally Posted by JBeaucaire

Let's see your code. When I run it, I only get two lines of 6/4/2010.

Let's see it.

OK, keep at it. The code is correct, the only issue now is your edits for each separate macro.

Um, unless you're DELETING the sheet created by the first successful export... when you enter a range of dates that results in NO RESULTS, it is not writing out an empty workbook, it just exits. So you're looking at the file that was already on the hard drive. Hehe.

Try adding this:

With Sheets("FnasT")
    .AutoFilterMode = False
    .Rows("7:7").AutoFilter

Running Autofilter code over and over again in succession and stopping in the middle sometimes leaves your sheets in odd states. This may help a little.

This is the macro exactly like I've presented and it seems to work. You getting different results is a serious concern.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.