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


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
 
Ad

Advertisements

Joined
May 31, 2011
Messages
32
Reaction score
0
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
 
Joined
Feb 27, 2012
Messages
5
Reaction score
0
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.
 
Joined
Feb 27, 2012
Messages
5
Reaction score
0
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.
 
Joined
May 31, 2011
Messages
32
Reaction score
0
Hi pompeymick,

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

Regards,
Sifou
 
Joined
Feb 27, 2012
Messages
5
Reaction score
0
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
 
Ad

Advertisements

Joined
May 31, 2011
Messages
32
Reaction score
0
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
 
Ad

Advertisements

Joined
Feb 27, 2012
Messages
5
Reaction score
0
Hi Sifou

Thankyou very much for that Sifou, it works like a dream now just as i hoped it would.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top