Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on Macros
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on Macros
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on Macros
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on Macros
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on Macros
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... "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros: can you copy macros from one doc to another? | Excel Discussion (Misc queries) | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions |