Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro to insert and move columns
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 112
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 112
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
cant insert a row it says cant move objects off sheet? Wick Const. Excel Discussion (Misc queries) 2 July 1st 08 02:55 PM
move multiple columns into single set of columns mrg9999 Excel Discussion (Misc queries) 1 September 25th 07 05:08 AM
unable to insert columns in excel, insert- columns (disabled) iam_leearner Excel Discussion (Misc queries) 1 August 13th 06 02:26 PM
insert columns macro is putting 2 columns instead of 1 AGH Excel Worksheet Functions 2 February 27th 06 02:36 PM
insert column move object markg Excel Discussion (Misc queries) 3 April 26th 05 03:52 PM


All times are GMT +1. The time now is 10:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement