Struggling with how to select cells by date and copy / paste

[mikeyboy]

Hi, Your assistance is much appreciated. I'm looking for code to copy "Cond Storage Sheet" cells A:F that have date/time equal to and ahead of current time. Cell A is the date and cell B is the time and paste the selected cells to "Pumping Report" cell C30.

The "Pumping Report" sheet is a handout given to controllers to perform actions according to the date and time annotated in cells A & B and extends as much as 48 hours into the future.

The "Cond Storage Sheet" is an ever increasing log of all the scheduled actions and is only added to 1 or 2 days in advance. Copying future date entries from the log is a convenient way to produce the required handout.

Thanks in advance

[Tom Schreiner]

On 2002-04-27 04:29, mikeyboy wrote:
Hi, Your assistance is much appreciated. I'm looking for code to copy "Cond Storage Sheet" cells A:F that have date/time equal to and ahead of current time. Cell A is the date and cell B is the time and paste the selected cells to "Pumping Report" cell C30.

The "Pumping Report" sheet is a handout given to controllers to perform actions according to the date and time annotated in cells A & B and extends as much as 48 hours into the future.

The "Cond Storage Sheet" is an ever increasing log of all the scheduled actions and is only added to 1 or 2 days in advance. Copying future date entries from the log is a convenient way to produce the required handout.

Thanks in advance

Hi Mike...
To cell C30?
Is this always the same or will
the data need to be pasted on down
on an ongoing basis?

In essence, is this a one time paste to
"Pumping Report" cell C30?

Thanks,
Tom

[mikeyboy]

C30 is always the same. I will be pasting over the previous "Pumping Report" C30 data with the latest and then printing out "Pumping Report" sheet to hand out to controllers.
Thanks

[ This Message was edited by: mikeyboy on 2002-04-27 04:56 ]

[Tom Schreiner]

One more quick question...
I'm drumming up a procedure for you and wanted to make sure I understood the placement on "Pumping Report".

I'm assuming columns C to H?
Also, which row, on "Cond Storage Sheet", contains your first entry?
Another ?
Does the data need to be sorted on
"Pumping Report"? If so, by date and time?

Thanks,
Tom

[ This Message was edited by: TsTom on 2002-04-27 05:00 ]

[mikeyboy]

Tom, First entry on "Cond Storage Sheet" is A11.

And yes C:H is correct

The rows of data are entered from earlier to later on "Cond Storage Sheet" and they will need to be in the same early to later row order on "Pumping Report"

[ This Message was edited by: mikeyboy on 2002-04-27 05:05 ]

[Tom Schreiner]

Run from "Cond Storage Sheet"
This appears to work...
If not, repost and someone
will help you...
Tom

Sub CopyByDate()

Dim LastEntry As Long, Cntr As Long

Dim CheckDate As Variant

Dim IsCheckDate As Date

Dim PlaceCntr As Integer



LastEntry = Cells(Rows.Count, 1).End(xlUp).Row

PlaceCntr = 29

Sheets("Pumping Report").Range("C30:H1000").ClearContents

For Cntr = 11 To LastEntry

    CheckDate = Cells(Cntr, 1) & " " & Format(Cells(Cntr, 2), "HH:MM")

    If IsDate(CheckDate) Then

        IsCheckDate = CheckDate

        If Now > IsCheckDate Then

            PlaceCntr = PlaceCntr + 1

            Range("A" & Cntr & ":F" & Cntr).Copy _

            Destination:=Sheets("Pumping Report").Range("C" & _

            PlaceCntr & ":H" & PlaceCntr)

        End If

    End If

Next

End Sub

[mikeyboy]

Tom, I pasted your macro in the "Cond Storage Sheet" and ran the macro.

Two adjustments are needed.
1) Previous date/time cells are being pasted instead of future date/times.
2) Can the paste be changed to paste special so the format isn't carried along with the copied cells?

Thank you so much for helping here. I'll check back later.

[Tom Schreiner]

Sub CopyByDate()

Dim LastEntry As Long, Cntr As Long

Dim CheckDate As Variant

Dim IsCheckDate As Date

Dim PlaceCntr As Integer



LastEntry = Cells(Rows.Count, 1).End(xlUp).Row

PlaceCntr = 29

Sheets("Pumping Report").Range("C30:H1000").ClearContents

For Cntr = 11 To LastEntry

    CheckDate = Cells(Cntr, 1) & " " & Format(Cells(Cntr, 2), "HH:MM")

    If IsDate(CheckDate) Then

        IsCheckDate = CheckDate

        If Now <= IsCheckDate Then

            PlaceCntr = PlaceCntr + 1

            Sheets("Pumping Report").Range("C" & _

            PlaceCntr & ":H" & PlaceCntr) = _

            Range("A" & Cntr & ":F" & Cntr).Value

        End If

    End If

Next

End Sub

[ This Message was edited by: TsTom on 2002-04-27 06:04 ]

[mikeyboy]

Tom, Sheer genious!

Works terrific!!

Thank you so much.

[mikeyboy]

This excel sheet is a work in progress. The next step is to mesh these two macros so both can function as change events if possible. The first macro is to fill in time cells using keypad only. The second pastes certain cells to a different sheet for printout. Any ideas?

Thanks again


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("B1:B1000,I1:I1000,P1:P1000")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub





Sub CopyByDate()
Dim LastEntry As Long, Cntr As Long
Dim CheckDate As Variant
Dim IsCheckDate As Date
Dim PlaceCntr As Integer

LastEntry = Cells(Rows.Count, 1).End(xlUp).Row
PlaceCntr = 29
Sheets("Pumping Report").Range("C30:H1000").ClearContents
For Cntr = 11 To LastEntry
CheckDate = Cells(Cntr, 1) & " " & Format(Cells(Cntr, 2), "HH:MM")
If IsDate(CheckDate) Then
IsCheckDate = CheckDate
If Now <= IsCheckDate Then
PlaceCntr = PlaceCntr + 1
Sheets("Pumping Report").Range("C" & _
PlaceCntr & ":H" & PlaceCntr) = _
Range("A" & Cntr & ":F" & Cntr).Value
End If
End If
Next
End Sub



[ This Message was edited by: Mikeyboy on 2002-04-27 13:15 ]