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
Pat-UK - 14 Mar 2008 12:15 GMT
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
'Dave[_6_ Wrote:
> ;639151']Hi Pat,
> The following code should do what you want. It creates a new sheet,
[quoted text clipped - 43 lines]
> Worksheets(D).Activate
> End Su
--
Pat-UK
Dave - 14 Mar 2008 21:21 GMT
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 - 15 Mar 2008 09:46 GMT
Yes I did notice this and corrected it when I first tried it, so it's
not that.
Thanks Once again for checking.
Pat
'Dave[_6_ Wrote:
> ;639775']Hi Pat,
> Did you get an error message when you ran the macro?
[quoted text clipped - 9 lines]
> Not sure if this will solve the problem. Let me know.
> Regards - Dave.

Signature
Pat-UK
Pat-UK - 16 Mar 2008 15:23 GMT
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

Signature
Pat-UK
Pat-UK - 17 Mar 2008 13:02 GMT
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
--
Pat-UK