Assistance with code

[keilah]

Hi the following VBA code is working fine......just cannot stop it from doing one thing.....i am sure this is a walk in the park for some of you people out there.....

"i need i the code to stop or delete the posting in cell 1 after the macro has run"........

Currently it is giving me a value in cell 1 "in the column" i place the cursor in and also the same value in cell 1 on the copy over.......

here is the code

Sub copyover()

Dim ac As Range
Set ac = ActiveCell
Dim col, NewRow As Integer

col = ac.Column
NewRow = Range("AF3").Value

Cells(1, col).Formula = "=VLOOKUP($B8,'[RevalComparisonModel.xls]IncomeReport-y'!$B$6:$N$" & NewRow & ",12,0)"
Cells(1, col + 1).EntireColumn.Insert Shift:=xlToRight
Cells(1, col).EntireColumn.Select
Cells(1, col).EntireColumn.Copy

Cells(1, col + 1).Select
ActiveSheet.Paste
End Sub

[Bob Phillips]

[vba]

Sub copyover()
Dim ac As Range
Set ac = ActiveCell
Dim col, NewRow As Integer

col = ac.Column
NewRow = Range("AF3").Value

Cells(1, col).Formula = "=VLOOKUP($B8,'[RevalComparisonModel.xls]IncomeReport-y'!$B$6:$N$" & NewRow & ",12,0)"
Cells(1, col + 1).EntireColumn.Insert Shift:=xlToRight
Cells(1, col).EntireColumn.Copy

Cells(1, col + 1).Select
ActiveSheet.Paste

Cells(1, col).ClearContents
End Sub
[/vba]

[keilah]

Thanks mate.....

execellent feedback.....work fine........