Sort by Date and Copy results to another sheet
 

I'm still working on my little project and can't seem to find what i'm looking for in any of the forums or help files, I know it can be done as I did it a college.

using the following data example

Date Time Name details

11/03/08 17:00 greg wanted to party
12/03/08 10:55 fred wanted help
13/03/08 11:45 paul wanted to speak to dave
13/03/08 13:30 harry wanted to go home

I want to leave the data intact and un edited in the main sheet above, and have a way to find all the data with todays date and copy them to another sheet at the push of a single button.

So i'd get a report on sheet 3

13/03/08 11:45 paul wanted to speak to dave
13/03/08 13:30 harry wanted to go home

Any ideas or good places to look for what I need.

Many Thanks

Sort by Date and Copy results to another sheet
 

Hi Pat,
The following code should do what you want. It creates a new sheet,
names it with today's date, and then fills in the right info.
Just copy and paste it into a VBA module.
The macro assumes the following:
That you will run the macro when the sheet containing your info, is
active.
That the dates to be searched are in column A, starting in row 2.
See comment in code to change this if necessary.
It's untidy, and jumps around while it's working, but it's all I have
time for.
If you want to do this more than once in a day, you have to delete the
new sheet first.
Regards - Dave.

Sub DoWhatPatWants()
A = Day(Now)
B = Month(Now)
C = Year(Now)
D = A & "-" & B & "-" & C
E = ActiveSheet.Name
G = 2 'First Paste Row
Sheets.Add
ActiveSheet.Name = D
Range("A1") = "Date"
Range("B1") = "Time"
Range("C1") = "Name"
Range("D1") = "Details"
Worksheets(E).Activate
F = 2 'Starting row. Change this if necessary.
H = 1 'Column containing dates (A=1, B=2 etc) Change this if
necessary.
Do Until Cells(F, H) = ""
If Cells(F, H).Value = Date Then
Range("A2:D2").Copy
Worksheets(D).Activate
Cells(G, 1).Select
ActiveSheet.Paste
Selection.EntireColumn.AutoFit
Application.CutCopyMode = False
G = G + 1
End If
F = F + 1
Worksheets(E).Activate
Loop
Worksheets(D).Activate
End Sub

Many Thanks Dave

I'm Halfway there

It Created the new sheet perfectly, but I couldn't get it to copy the data over, I even tried creating a new sheet with the sample data as posted here, with the date in column A and changed the dates to todays.

Many Thanks

Sort by Date and Copy results to another sheet
 

Hi Pat,
Did you get an error message when you ran the macro?

I noticed when I checked the posting, that the format of this forum
has wrapped one of the code lines because it was too long.
The following code line should be on only one line in your VBA macro,
not wrapped onto a second line as it appears he

H = 1 'Column containing dates (A=1, B=2 etc) Change this if
necessary.

Not sure if this will solve the problem. Let me know.
Regards - Dave.

Yes I did notice this and corrected it when I first tried it, so it's not that.

Thanks Once again for checking.

Pat

OK I think I know why it's not working.

The Sheet it set as a database sheet so the columns have names, the date one is called Call_Date, will this make a difference to the required code ??

Many Thanks

WOO HOO,

I did it, I had a play with advance filter and after quite a few date issues i worked out how to get it to work,

I was using =now() in the form to put the date in the Database sheet and then formatting the cell in the database sheet to show date only, What I should have done was used =Int(now)) in the form,

Once I worked out that part was giving me the problems, advanced filter using dates turned out to be not as problematic as I first thought.

Thanks again everyone.