PDA

View Full Version : Assistance with code



keilah
08-23-2007, 01:05 AM
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
08-23-2007, 01:09 AM
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

keilah
08-23-2007, 01:16 AM
Thanks mate.....

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