Help on Macros

[Yuvaraj]

Hi,

I have a file with huge data. I would like to insert a column before a
column with heading "8010" and the new column which is been inserted should
contain the sum of all datas to its left..
Assuming column BX is the "8010"( At BX2) column i would like to insert a
new column at BW and it should contain sum of datas from B2 to BW2

 

[Joel]

It could easily be done in a macro but even easier with a formula

1) Manually add a column before column BX by highlight column BX by clicking
on BX at the top of the column. then right click mouse and select Insert

2) in cell BX2 put the following formula

=if(BY2=8010,sum(B2:BW2),"")

3) Copy the formula down column BX.

 

[Yuvaraj]

But there are many such columns. and i do it on a daily basis... I thought
if i could write a macro, i dont have to type the formulas again and again...

 

[Joel]

Good answer. If yo are repeating operations over and over again then you
should use macros.

Sub SumAllData()

'Insert Column
With ActiveSheet
.Columns("BX").Insert
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("BX2").Formula = "=if(BY2=8010,SUM(B2:BW2),"""")"
.Range("BX2").Copy Destination:=.Range("BX3:BX" & LastRow)
End With
End Sub

 

[Yuvaraj]

Thanks Joel... It solves the issue for today... but each day the '8010'
appears in various coulmns... its not BX always... is there a way where in
the macro can search for 8010 and automatically insert a column before it...

Thanks in advance...