Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2008

Tip: Looking for answers? Try searching our database.

Sort by Date and Copy results to another sheet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pat-UK - 13 Mar 2008 11:39 GMT
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

Signature

Pat-UK

Dave - 13 Mar 2008 18:34 GMT
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

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.