Hi All
Still trying to learn VBA. Sorry.
I have got this code to work but after using the command button wit
code I got from Dave the date is still being displayed.
Here is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C6")) Is Nothing Then
With Target
.Offset(-2, 0).Value = Format(Time, "hh:mm")
.Offset(-3, 0).Value = Format(Date, "dd/mm/yyyy")
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Now it works fine when I open the sheet but when I use the Code tha
Dave gave me below:
Private Sub CommandButton3_Click()
Dim Summary As Worksheet
Dim myFromAddr As Variant
Dim myToRow As Variant
Dim iCtr As Long
Dim LastCol As Range
Dim NextColNum As Long
myToRow = Array(1, 2, 3, 4, 5, 6, _
8, 9, 10, 11, 12, _
14, 15, 16, 18, 19, _
20, 22, 23, 25, 27)
myFromAddr = Array("C3", "C4", "C5", "C6", "c7", "D3", _
"D15", "D16", "D17", "D18", "D19", _
"D22", "D23", "D24", "D27", "D28", _
"D29", "D32", "D33", "D36", "c40")
If UBound(myToRow) < UBound(myFromAddr) Then
MsgBox "Design error--not same number of cells!"
Exit Sub
End If
If IsEmpty(Me.Range(myFromAddr(LBound(myFromAddr)))) Then
MsgBox "Please fill in cell: "
myFromAddr(LBound(myFromAddr))
Exit Sub
End If
Set Summary = Worksheets("Summary")
With Summary
Set LastCol _
= .Cells(myToRow(LBound(myToRow)), .Columns.Count).End(xlToLeft)
If IsEmpty(LastCol) Then
NextColNum = LastCol.Column
Else
NextColNum = LastCol.Column + 1
End If
For iCtr = LBound(myToRow) To UBound(myToRow)
.Cells(myToRow(iCtr), NextColNum).Value _
= Me.Range(myFromAddr(iCtr)).Value
Me.Range(myFromAddr(iCtr)).ClearContents
Next iCtr
End With
End Sub
So now the first sheet clears but the date and time stay.
How can I clear the date and time after the sheet is cleared and tfre
to the summary sheet?
thx guy
--
Mikeic
-----------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...fo&userid=2246
View this thread:
http://www.excelforum.com/showthread.php?threadid=37500