I can't figure out what you are doing but try this

Sub fff()
Application.ScreenUpdating = False
Dim intNumber, mc As Double
Dim lrow As Long
mc = 4 '"d"
lrow = Cells(Rows.Count, mc).End(xlUp).Row - 1
Columns(mc).Insert
intNumber = InputBox("Vary Number By How Much?", "Variation")
Cells(2, mc).FormulaR1C1 = "=RC[1]+" & intNumber
Cells(2, mc).AutoFill Destination:=Cells(2, mc).Resize(lrow)
Cells(1, mc + 1).Cut Destination:=Cells(1, mc)
Cells(1, mc).Copy Cells(1, mc + 1)
Columns(mc + 1).Value = Columns(mc).Value
Columns(mc).Delete
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Colin Hayes" wrote in message
...

Hi

My code below uses columns D and then E to allow a number change via a
Popup.

I need to make this more generic if possible , and instead of having
specific columns I'd like it to work on whichever column I select prior to
running the code.

Can anyone help amend the code below to accommodate this?

Grateful for any help. lrow is a variable representing the last cell in
the column.




Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
Dim intNumber As Double
intNumber = InputBox("Vary Number By How Much?", "Variation")
ActiveCell.FormulaR1C1 = "=RC[1]+" & intNumber
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & lrow),
Type:=xlFillDefault
Range("D2:D" & lrow).Select
Range("E1").Select
Selection.Cut Destination:=Range("D1")
Range("D1").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft