Prev: Macro/ custom filter, criteria="abc" or blank
Next: convert figure into word
From: Alex on 17 Feb 2010 06:16
Hi,

I have a macro that susscessfulling inserts any number of columns, however
it is SUPPOSED to keep all the formulas and clear the contents of the
inserted columns, which it does do, so that is problem number 1.

Now what i need is for the total column to be updated automatically with the
number of columns inserted. The total column has a formulae of what ever that
rows entered value is multipied by the header row value users select for that
column.

if this possibe?

here is what i have so far:
Sub InsertColAndFillFormulas(Optional vCols As Long = 0)

Dim x As Long
ActiveCell.EntireColumn.Select
If vCols = 0 Then
vCols = Application.InputBox(prompt:= _
"How many columns do you want to add?", Title:="Add Columns", _
Default:=1, Type:=1) 'Default for 1 col, type 1 is number
If vCols = 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:=vCols).Insert

Selection.AutoFill Selection.Resize( _
Columnsize:=vCols + 1), xlFillDefault

On Error Resume Next
Selection.Offset(1).Resize(vCols).EntireColumn. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub
 | 
Pages: 1
Prev: Macro/ custom filter, criteria="abc" or blank
Next: convert figure into word