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

mikeyboy

Board Regular
Joined
Apr 26, 2002
Messages
57
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Run from "Cond Storage Sheet"
This appears to work...
If not, repost and someone
will help you...
Tom
<pre>


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


</pre>
 
Upvote 0
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.
 
Upvote 0
<pre>


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</pre>
This message was edited by TsTom on 2002-04-27 06:04
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top