insert column macro will incorrectly add another value

[davechlan]

I have a macro that will prompt the user for number of columns to
insert. Each of the inserted columns needs to keep the same formulas,
but delete the constants. When the macro is run the formulas remain
correct however if the original column had a 5 in it the new column
will show a 6 in the same row on the new column. Example:
Column A Added column B Added Column C
=sum(A2:A5) =sum(A2:A5) =sum(A2:A5)
1 2 3
2 3 4
3 4 5

If anyone could determine why it is showing the constants + 1 and
revise the code I would appreciate it, or has a better way to achieve
the same objective.

Sub InsertColumnsAndFillFormulas(Optional vColumn As Long = 0)
Dim x As Long
ActiveCell.EntireColumn.Select 'So you do not have to preselect
entire row
If vColumns = 0 Then
vColumns = Application.InputBox(prompt:= _
"How many columns do you want to add?", Title:="Add Columns", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vColumns = False Then Exit Sub
End If

Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

x = Sheets(sht.Name).UsedRange.Columns.Count 'lastcell fixup

Selection.Resize(columnsize:=2).Columns(2).EntireColumn. _
Resize(columnsize:=vColumns).Insert Shift:=xlToRight

Next sht
Worksheets(shts).Select
End Sub

Thanks for all help!

 

[Dave Peterson]

First, when I ran your code, the inserted columns were empty.

I tried toggling tools|Options|edit tab|Extend data range formats and formulas,
but that didn't change anything for me. The inserted columns were still empty.

But....

If you record a macro when you do this:

Select the inserted columns
edit|goto|special|check constants
then hit the delete key on the keyboard

You'll have code that clears those constants.