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
|