CODEWITHSUNDEEP
vbaexcel
deejay
deejay

Reputation: 57

Add new column including formula

Good Day,

I am trying to add a column on the sheet that I am creating including the formula of the selected column. As per checking surfing for some codes I saw this,

Sub InsertRows() 

Dim Rng As Long 
Dim lngA As Long 
Dim lngB As Long 

Application.ScreenUpdating = False 

Rng = InputBox("Enter number of rows required.") 

If Rng = 0 Then Exit Sub 

Range(ActiveCell.Offset(1), ActiveCell.Offset(Val(Rng), 0)).EntireRow.Insert 

 '// How many formulas To copy down?
 '// From A  To last entry In row.
lngB = ActiveCell.Row 
lngA = Cells(lngB, Columns.Count).End(xlToLeft).Column 
Range(Cells(lngB, 1), Cells(lngB + Val(Rng), lngA)).FillDown

this code really suits my need, however after trying several times, i am not able to convert it as column function. may I ask for your help to convert this? thank you so much

Best regards,

Upvotes: 0

Views: 58

Answers (1)

Limak
Limak

Reputation: 1521

Check this out:

Sub InsertCols()
Dim Rng As Long

Application.ScreenUpdating = False

Rng = InputBox("Enter number of rows required.")

If Rng = 0 Then Exit Sub

Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, Val(Rng))).EntireColumn.Insert

ActiveCell.EntireColumn.Copy
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, Val(Rng))).EntireColumn.PasteSpecial xlPasteAll
Application.CutCopyMode = False

End Sub

Upvotes: 1

Related Questions