Hi Rain,
My best guess is:
Sub Macro1(strSheet As String, strFormatRange As String, strCol As String)
If strSheet = "Select Car" Then
Sheets(strSheet).Columns(strCol).Copy
ActiveSheet.Paste Destination:= _
Sheets("Sheet2").Range("A1")
Application.CutCopyMode = False
Columns("B:B").Insert shift:=xlToRight
With Range(strFormatRange)
.NumberFormat = "dd/mm/yyyy;@"
.Cells(1).FormulaR1C1 = "=RC[-1]+1" '<<======= CHANGE
.Cells(1).AutoFill Destination:=Range(strFormatRange)
End With
End If
End Sub
You need to change the formula in the line marked
'<<======= CHANGE
The above formula is just a plug used for testing purposes.
As I do not know your data layout, or what you object is, I have had to make
cetain assumptions which may well be erroneous.
So please test on a *copy* of your data!
---
Regards,
Norman
"Rain" wrote in message
...
Hi Norman,
Here is the macro that I am using:
Code Excerpt :
---------------
Sub Macro1(strSheet As String, strFormatRange as String, strCol as String)
If strSheet = "Select Car" Then
Sheets(strSheet).Columns(strcol).Copy
ActiveSheet.Paste Destination:= _
Sheets("Sheet2").Range("A1")
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert shift:=xlToRight
Selection.NumberFormat = "dd/mm/yyyy;@"
ActiveCell.FormulaR1C1 = _
"=DATEVALUE(LE --- removed to keep it short --- RC2,4))"
Range("B1").Select
Selection.AutoFill Destination:=Range(strFormatRange)
End Sub
Original Code excerpt:
--------------------------
Sub Macro1(strSheet As String, strFormatRange as String, strCol as String)
If strSheet = "Select Car" Then
Sheets(strSheet).Select
Columns(strCol).Select
Selection.Copy
Range("A1").Select
*** Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert shift:=xlToRight
Selection.NumberFormat = "dd/mm/yyyy;@"
ActiveCell.FormulaR1C1 = _
"=DATEVALUE(LE --- removed to keep it short --- RC2,4))"
Range("B1").Select
Selection.AutoFill Destination:=Range(strFmtRange)
End Sub
Regards,
Rain
|