macro - copy, paste, convert column data

[Johnny]

I have data as follows:

I J
Inv Date SO No.
08-Jun-06 5
05-Jan-05 3
01-Nov-05 4
05-Jan-04 2

The data in column 'I' can vary from 10 to 1000 records depending on the
years select. I have recorded a macro to copy column I to K which still shows
the date format dd-mmm-yy. Recorded another macro to convert Col K to show
only ‘YYYY’ format in Col L and then copy Col L and Paste Special as Value
into Col M.
I am not sure how to get the macro to select the data available from Col K
and copy to Col L. I have used the AutoFill and that is the problem - the
data fills only up to those cells and not any cells after that.

Is there an easier way of writing the macro to get it to select whatever
data there is in Col I, copy it to Col K showing only the year format.?
Would appreciate any help possible.

I J K L M
Inv Date SO No. FirstYear
08-Jun-06 5 08-Jun-06 2006 2006
05-Jan-05 3 05-Jan-05 2005 2005
01-Nov-05 4 01-Nov-05 2005 2005
05-Jan-04 2 05-Jan-04 2004 2004



Sub CopyInvDate()

Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Range("K1").Select
ActiveSheet.Paste
Range("K1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "FirstYear"
Range("K2").Select
Call CopyDateConvert
End Sub

Sub CopyDateConvert()
Range("L2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""yyyy"")"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L278")
Range("L2:L278").Select
Selection.Copy
Range("M2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[JLGWhiz]

Try this:

Sub dtConv()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
Range("$K$1").Activate
c = Cells(i, 11).Offset(0, -2).Value
Cells(i, 11) = Format(c, "YYYY")
Next
End Sub

 

[JLGWhiz]

Disregard the first procedure. I did it in haste and did not fully test it.
Use this one.

Sub dtConv()
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
Range("$K$1").Activate
For i = 1 To LastRow
c = Cells(i, 11).Offset(0, -2).Value
Cells(i, 11) = Format(c, "YYYY")
Next
End Sub

Make sure Column K is formatted as "General".

This will work with varying lengths for column "I".

 

[Johnny]

Thanks JLGWhiz that has helped. I ran another report 40K lines and it worked
well.
I have put in the following code at the beginning just after sub
Columns("K:K").Select
Selection.NumberFormat = "General"
and at the end after next
Range("K1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("K2").Select

Once again thanks - I appreciate it

Disregard the first procedure. I did it in haste and did not fully test it.
Use this one.

Sub dtConv()
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
Range("$K$1").Activate
For i = 1 To LastRow
c = Cells(i, 11).Offset(0, -2).Value
Cells(i, 11) = Format(c, "YYYY")
Next
End Sub

Make sure Column K is formatted as "General".

This will work with varying lengths for column "I".

I have data as follows:

I J
Inv Date SO No.
08-Jun-06 5
05-Jan-05 3
01-Nov-05 4
05-Jan-04 2

The data in column 'I' can vary from 10 to 1000 records depending on the
years select. I have recorded a macro to copy column I to K which still shows
the date format dd-mmm-yy. Recorded another macro to convert Col K to show
only ‘YYYY’ format in Col L and then copy Col L and Paste Special as Value
into Col M.
I am not sure how to get the macro to select the data available from Col K
and copy to Col L. I have used the AutoFill and that is the problem - the
data fills only up to those cells and not any cells after that.

Is there an easier way of writing the macro to get it to select whatever
data there is in Col I, copy it to Col K showing only the year format.?
Would appreciate any help possible.

I J K L M
Inv Date SO No. FirstYear
08-Jun-06 5 08-Jun-06 2006 2006
05-Jan-05 3 05-Jan-05 2005 2005
01-Nov-05 4 01-Nov-05 2005 2005
05-Jan-04 2 05-Jan-04 2004 2004



Sub CopyInvDate()

Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Range("K1").Select
ActiveSheet.Paste
Range("K1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "FirstYear"
Range("K2").Select
Call CopyDateConvert
End Sub

Sub CopyDateConvert()
Range("L2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""yyyy"")"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L278")
Range("L2:L278").Select
Selection.Copy
Range("M2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub