Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert and move columns
Hi
Hopefully you can help please... I need to compare Budget v Actuals. Currently the spreadsheet appears similar to below: Budget Actual Region A Region B Region C Region A Region B Region C ie Regions are grouped under Budget and Actual What I require is... Region A Bud Region A Act Variance Region B Bud Region B Act Varianc etc I have recorded a very basic macro that cuts and inserts columns. It does not have the variance column included at the moment. There has to be a better way!! Columns("C:L").Select Range("C2").Activate Selection.Insert Shift:=xlToRight Columns("M:M").Select Range("M2").Activate Selection.Cut Columns("C:C").Select Range("C2").Activate Selection.Insert Shift:=xlToRight Columns("AH:AH").Select Range("AH2").Activate Selection.Cut Columns("D:D").Select Range("D2").Activate Selection.Insert Shift:=xlToRight Thanks in advance Andrea |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert and move columns
I simplified the macro. I comment out the old code and show the new code
below the comments 'Columns("C:L").Select 'Range("C2").Activate 'Selection.Insert Shift:=xlToRight Columns("C").Insert 'Columns("M:M").Select 'Range("M2").Activate 'Selection.Cut Columns("M:M").Cut 'Columns("C:C").Select 'Range("C2").Activate 'Selection.Insert Shift:=xlToRight Columns("C:C").insert Shift:=xlToRight 'Columns("AH:AH").Select 'Range("AH2").Activate 'Selection.Cut Columns("AH:AH").Cut 'Columns("D:D").Select 'Range("D2").Activate 'Selection.Insert Shift:=xlToRight Columns("D:D").insert Shift:=xlToRight "andrea" wrote: Hi Hopefully you can help please... I need to compare Budget v Actuals. Currently the spreadsheet appears similar to below: Budget Actual Region A Region B Region C Region A Region B Region C ie Regions are grouped under Budget and Actual What I require is... Region A Bud Region A Act Variance Region B Bud Region B Act Varianc etc I have recorded a very basic macro that cuts and inserts columns. It does not have the variance column included at the moment. There has to be a better way!! Columns("C:L").Select Range("C2").Activate Selection.Insert Shift:=xlToRight Columns("M:M").Select Range("M2").Activate Selection.Cut Columns("C:C").Select Range("C2").Activate Selection.Insert Shift:=xlToRight Columns("AH:AH").Select Range("AH2").Activate Selection.Cut Columns("D:D").Select Range("D2").Activate Selection.Insert Shift:=xlToRight Thanks in advance Andrea |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert and move columns
Thanks Joel, but there must be an easier way.
At times the columns can represent up to 20 regions, so this process would have to be done 40 times and that doesn't take into account adding the variance column which is not included in the recorded macro at the moment. Andrea "Joel" wrote: I simplified the macro. I comment out the old code and show the new code below the comments 'Columns("C:L").Select 'Range("C2").Activate 'Selection.Insert Shift:=xlToRight Columns("C").Insert 'Columns("M:M").Select 'Range("M2").Activate 'Selection.Cut Columns("M:M").Cut 'Columns("C:C").Select 'Range("C2").Activate 'Selection.Insert Shift:=xlToRight Columns("C:C").insert Shift:=xlToRight 'Columns("AH:AH").Select 'Range("AH2").Activate 'Selection.Cut Columns("AH:AH").Cut 'Columns("D:D").Select 'Range("D2").Activate 'Selection.Insert Shift:=xlToRight Columns("D:D").insert Shift:=xlToRight "andrea" wrote: Hi Hopefully you can help please... I need to compare Budget v Actuals. Currently the spreadsheet appears similar to below: Budget Actual Region A Region B Region C Region A Region B Region C ie Regions are grouped under Budget and Actual What I require is... Region A Bud Region A Act Variance Region B Bud Region B Act Varianc etc I have recorded a very basic macro that cuts and inserts columns. It does not have the variance column included at the moment. There has to be a better way!! Columns("C:L").Select Range("C2").Activate Selection.Insert Shift:=xlToRight Columns("M:M").Select Range("M2").Activate Selection.Cut Columns("C:C").Select Range("C2").Activate Selection.Insert Shift:=xlToRight Columns("AH:AH").Select Range("AH2").Activate Selection.Cut Columns("D:D").Select Range("D2").Activate Selection.Insert Shift:=xlToRight Thanks in advance Andrea |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert and move columns
I put the following dat in the worksheet
G1 = 1 J1 = 2 M1 = 3 P1 = 4 Then ran code below For i = Columns("P").Column To Columns("G").Column Step -2 Columns(i).Cut Columns("C").Insert Shift:=xlToRight Next i "andrea" wrote: Thanks Joel, but there must be an easier way. At times the columns can represent up to 20 regions, so this process would have to be done 40 times and that doesn't take into account adding the variance column which is not included in the recorded macro at the moment. Andrea "Joel" wrote: I simplified the macro. I comment out the old code and show the new code below the comments 'Columns("C:L").Select 'Range("C2").Activate 'Selection.Insert Shift:=xlToRight Columns("C").Insert 'Columns("M:M").Select 'Range("M2").Activate 'Selection.Cut Columns("M:M").Cut 'Columns("C:C").Select 'Range("C2").Activate 'Selection.Insert Shift:=xlToRight Columns("C:C").insert Shift:=xlToRight 'Columns("AH:AH").Select 'Range("AH2").Activate 'Selection.Cut Columns("AH:AH").Cut 'Columns("D:D").Select 'Range("D2").Activate 'Selection.Insert Shift:=xlToRight Columns("D:D").insert Shift:=xlToRight "andrea" wrote: Hi Hopefully you can help please... I need to compare Budget v Actuals. Currently the spreadsheet appears similar to below: Budget Actual Region A Region B Region C Region A Region B Region C ie Regions are grouped under Budget and Actual What I require is... Region A Bud Region A Act Variance Region B Bud Region B Act Varianc etc I have recorded a very basic macro that cuts and inserts columns. It does not have the variance column included at the moment. There has to be a better way!! Columns("C:L").Select Range("C2").Activate Selection.Insert Shift:=xlToRight Columns("M:M").Select Range("M2").Activate Selection.Cut Columns("C:C").Select Range("C2").Activate Selection.Insert Shift:=xlToRight Columns("AH:AH").Select Range("AH2").Activate Selection.Cut Columns("D:D").Select Range("D2").Activate Selection.Insert Shift:=xlToRight Thanks in advance Andrea |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cant insert a row it says cant move objects off sheet? | Excel Discussion (Misc queries) | |||
move multiple columns into single set of columns | Excel Discussion (Misc queries) | |||
unable to insert columns in excel, insert- columns (disabled) | Excel Discussion (Misc queries) | |||
insert columns macro is putting 2 columns instead of 1 | Excel Worksheet Functions | |||
insert column move object | Excel Discussion (Misc queries) |