Switch [SOLVED]

[James Perry]

Hello friends.

Cells A2:A21 contain values like ExportRep1 thru ExportRep21.

Cols A1:C1 contain values like Jan Feb Mar

Data_Range B2:D22 contain names of 21 countries.

Here's the Q.

The countries in Jan r repeated in Feb and again in Mar.

How do i "switch" the Countries with Export Reps & vice versa.

It is assumed that the months r constant.

For the sake of keeping it simple there r 21 ExportReps and 21 Countries.

Pivot Table is an option.

But is there an alternate solution in Excel that can do the "switch" ???

Maybe like using a "macro button" and naming it "Switch". And then the switch takes place with just a single mouse-click on the button. I presume a Macro button would take just one sec with just a single m-click.

Thank u my friends in advance for the solution, ASAP!!!

Stay blessed.

James

 

[NARAYANK991]

Hi James ,


I don't understand your problem ; can you clarify ?


Do you mean to say that at present , the reps are the row headers , while the countries are the data ; when the switch takes place , the countries will become the row headers , and the reps will become the data ?


Narayan

 

[James Perry]

Hello Narayan & thank u 4 ur interest.

Its like this:

In columnA, A1:A21 i have GlobalSalesRep1 thru GlobalSalesRep21.

In columnB, B1:B21 i have the names of 21 countries. (each GSR represents just one country & that's fixed.)

For now, i'd like to do a "switch". That is, all the values in columnB will appear i columnA.

And if i do a "switch" again, then all the values return to their original state.

Can it be done using a "button" assigned 2 a macro.

So, i click on the button once and the 'switch' takes place. If i click on the button again, all the values return to their original state.

Thank u in advance, Sir.

James

 

[NARAYANK991]

Hi James ,


Try this :

[pre]

Public Sub Switch_Cols()
Dim Col_A As Range, Col_B As Range
Set Col_A = ActiveSheet.Range("A1:A21")
Set Col_B = ActiveSheet.Range("B1:B21")

Dim Store_It As Variant
Store_It = Col_A.Value

Application.ScreenUpdating = False
Application.EnableEvents = False

Col_A.Value = Col_B.Value
Col_B.Value = Store_It

Set Col_A = Nothing
Set Col_B = Nothing

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

[/pre]
You can insert the button , and assign this macro to it.


Narayan

 

[James Perry]

Very many thanks Narayan. That was so cool!

Stay blessed.

James