Sort by Date and Copy results to another sheet

[Pat-UK]

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

 

[Dave]

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("A22").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

 

[Pat-UK]

Many Thanks Dave

I'm Halfway there

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

Many Thanks

;639151']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("A22").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 Su

 

[Dave]

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 here:

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.

 

[Pat-UK]

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

Thanks Once again for checking.

Pat

;639775']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.

 

[Pat-UK]

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

 

[Pat-UK]

WOO HOO,

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

I was using =now() in the form to put the date in the Database shee
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 filte
using dates turned out to be not as problematic as I first thought.

Thanks again everyone