Need help in excel to copy multiple Rows and place date in cells

P

pompeymick

Joined
Feb 27, 2012
Messages
5
Reaction score
0
This is my first question on here so oplease be gentle with me.
I have some code below that allows me to insert some text in col a,b,c,d,e and f and the date is output in col g. This works fine for me but when i copy and paste more than one row of info the date is not input in either of the col g.

The answer must be easy but i am not sure and would welcome the help.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 10 Then Exit Sub
If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then
With Target(1, 7)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
.EntireColumn.AutoFit
End With
End If
End Sub
 
Hi pompeymick,

Have you tried using the debugger in the VB editor to see why it behaves differently?

Let me know and I'll try to give you a hand.... best would be to see your workbook, if no confidential data are in it. Thanks

Sifou
 
Hi Sifou

Thanks for taking time to help me, i have attached a copy of my worksheet. As you will see by looking at it, only the first line has a date in the correct cell. Hope worksheet is attached but cannot tel if it has or not.
 
Hi Sifou
For some reason i could not attach workbook, but if you just C&P the code which i have placed in my original post and place it into a workbook you will see what i mean, when you C&P more than one line to input the date.
 
Hi pompeymick,

Unfortunatelly it was not attached... can you send it to (e-mail address removed), Thanks

Regards,
Sifou
 
Your email was removed, mine is below if you can understand it fine i hope.
michaelhill5at
h
o
t
m
a
i
l
d
o
t
c
o
m
 
Hi,

The reason you are not populating the other date fields being because you are only writing to the hard-coded cell Target(1,7). the other reason, being that you need to loop through each changed row (as I understand from your initial post) as you do copy more than a row at a time, which therefore mean that the are more than one "Target" changed cells.

The issue you then face being that you are making changes to the spreadsheet in the Worksheet_Change Sub, which fires any time a cell is changed (even when changed by the sub itself; this is why I have wrapped the code changing the cells with the Application.EnableEvents statements (disable during the code updating the row G, and re-enable before exiting the Sub).

I hope this makes sense and helps you resolve your issue; let me know if you require further help.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 10 Then Exit Sub
If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then
Application.EnableEvents = False
For Each cell In Range(Cells(Target.Row, 1), Cells(Target.Row + Target.Rows.Count - 1, 1))
With Cells(cell.Row, 7)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
.EntireColumn.AutoFit
End With
Next cell
Application.EnableEvents = True
End If
End Sub


Sifou
 
Back
Top