Moving columns from one sheet to another based on user input

[mweigel]

I have a spreadsheet that contains all my data on sheet1. The
columns
have Years as headings and dollar amounts underneath them. I have a
drop down list for the user to pick the year from in cell R2.
Whatever year they choose I want the appropriate column to move to
sheet 3. Ex. if they choose 2009 I want Column M to move to sheet
3,
if they choose 2010 I want column N to move to sheet 3. Any ideas???

 

[Don Guillett]

try
Sub movecoltosamecolinsheet()
x = Range("r2") - 1997
Cells(1, x).EntireColumn.Cut Sheets("sheet3").Cells(1, x)
End Sub

 

[Gord Dibben]

To actually "move" the columns would require VBA, probably case select event
code.

I think you may mean "copy" the columns.

You could do that with some IF clauses. The IF's being the year chosen.

Enter this in A1 of Sheet3 and copy down as far as you need.

=IF(Sheet1!$R$2=2009,Sheet1!M1,IF(Sheet1!$R$2=2010,Sheet1!N1,""))

Adjust for up to 7 IF's

If you do mean "move", post back.


Gord Dibben MS Excel MVP