userform copy paste

chris2727272727

Board Regular
Joined
Jul 10, 2005
Messages
152
Dim a
Dim activecell

Dim keyboard
Dim tina
Dim bina


On Error Resume Next

i = 1
UserForm2.TextBox1.MultiLine = True
UserForm2.Caption = "First Aid Training Overdue"
lastrow = Range("c65536").End(xlUp).Row
For Each cell In Range("f9:m" & lastrow)
a = cell.Row
Application.ScreenUpdating = False
If cell.Value < DateTime.Date And cell.Value > DateTime.Date - 365 And cell.Font.Strikethrough = False Then
Application.Union(Range("a" & a, "d" & a), cell).Copy

UserForm2.TextBox1.Paste
End If
Next
Application.ScreenUpdating = True
UserForm2.Show
End Sub


I have the above code the trouble is that when it runs it copies all the line to the text box i only want it to copy say for example it picks an overdue date on "a7" i want it to copy a5:d5 inclusive and a8 but instead it copies all of the row to the left of the cell it finds ie a5:a8 inclusive.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, chris2727272727,

Seems to me like you are messing up some references (a5 ... a7 ?)in your explanation and also you write "left" while I think you mean "right". So perhaps I'm missing something?

this line can be problematic
Application.Union(Range("a" & a, "d" & a), cell).Copy
example: this will produce an error
Union(Range("A1:D1"), Range("D2")).Copy

please tell again what you're trying to do

kind regards,
Erik
 

chris2727272727

Board Regular
Joined
Jul 10, 2005
Messages
152
Copy Paste

It only copies when the condition is true ie it scolls through the sheet and copies when the condition is true
 

Watch MrExcel Video

Forum statistics

Threads
1,118,535
Messages
5,572,754
Members
412,482
Latest member
arooshrana2
Top