Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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






  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default




    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 ]

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, Sheer genious!

    Works terrific!!

    Thank you so much.

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •