Swap cells on Sheet 1 to swap columns on Sheet 2

[Naveed7991]

Hello,

I am naveed, this is the first time ever i am writing on a web portal for help. I hope this works out, below mentioned is my query.

1. Imagine below is the data that we have in Sheet 1

<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { text-align: center; border: 0.5pt solid windowtext; }.xl66 { text-align: center; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }</style> [TABLE="width: 260"]
<colgroup><col style="width:65pt" span="4" width="87"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 87"]A
[/TD]
[TD="class: xl66, width: 87"]B[/TD]
[TD="class: xl66, width: 87"]C[/TD]
[TD="class: xl66, width: 87"]D
[/TD]
[/TR]
[TR]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]2
[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]7
[/TD]
[TD="class: xl65"]6
[/TD]
[TD="class: xl65"]5
[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]32
[/TD]
[/TR]
</tbody>[/TABLE]


2. I would like to see the below view in Sheet 2 ( All the column headers)

<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { text-align: center; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }</style> [TABLE="width: 65"]
<colgroup><col style="width:65pt" width="87"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 87"]A
[/TD]
[/TR]
[TR]
[TD="class: xl65"]B
[/TD]
[/TR]
[TR]
[TD="class: xl65"]C
[/TD]
[/TR]
[TR]
[TD="class: xl65"]D
[/TD]
[/TR]
</tbody>[/TABLE]

3. Now if i swap any column in Sheet 2 as shown below.

<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { text-align: center; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }</style> [TABLE="width: 65"]
<colgroup><col style="width:65pt" width="87"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 87"]A
[/TD]
[/TR]
[TR]
[TD="class: xl65"]D
[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]B
[/TD]
[/TR]
</tbody>[/TABLE]

4. The data in Sheet 1 should be swapped as shown below

<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { text-align: center; border: 0.5pt solid windowtext; }.xl66 { text-align: center; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }</style> [TABLE="width: 260"]
<colgroup><col style="width:65pt" span="4" width="87"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 87"]A
[/TD]
[TD="class: xl66, width: 87"]D
[/TD]
[TD="class: xl66, width: 87"]C[/TD]
[TD="class: xl66, width: 87"]B
[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]7[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]32[/TD]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
</tbody>[/TABLE]

I hope this explains well, and to let you know if am not a Macro guy, i just do basic excel stuff. If there is any explanation to this request it to be posted in detail so that i can do it from my end.

 

[Rick Rothstein]

Give this macro a try...

Sub MoveColumnsOfDataAround()
  Dim R As Long, HeaderCount As Long, RowCount As Long, NewOrder As String, WS1 As Worksheet, WS2 As Worksheet
  Set WS1 = Sheets("Sheet1")
  Set WS2 = Sheets("Sheet2")
  RowCount = WS1.Cells(Rows.Count, "A").End(xlUp).Row
  HeaderCount = WS2.Cells(Rows.Count, "A").End(xlUp).Row
  For R = 1 To HeaderCount
    NewOrder = NewOrder & " " & WS1.Rows(1).Find(WS2.Cells(R, "A").Value, , xlValues, xlWhole, , , False, , False).Column
  Next
  NewOrder = Trim(NewOrder)
  WS1.Range("A1").Resize(RowCount, HeaderCount) = Application.Index(WS1.Cells, Evaluate("ROW(1:" & RowCount & ")"), Split(NewOrder))
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MoveColumnsOfDataAround) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro. Alternately, you can add a Forms CommandButton to the worksheet and assign this macro to it, then you would only have to click the button to run the macro.

 

[Naveed7991]

Thank you for your swift response and helping me out on how to use Macro.

I am using a MAC, i have refereed few online tutorials and added the code that you have mentioned. After running the code, column A vanished and nothing appeared in sheet 2.

 

[Rick Rothstein]

Thank you for your swift response and helping me out on how to use Macro.

I am using a MAC, i have refereed few online tutorials and added the code that you have mentioned. After running the code, column A vanished and nothing appeared in sheet 2.

First off, for any future questions you may ask, you need to mention that you are using a Mac and not a PC as there are differences between them that can affect the solutions you can use. Unfortunately, I do not have a Mac to test the code I posted on, so I am not sure what to tell you except that the code I posted works on my PC. This is a shot in the dark, but one thing you can try is changing the keyword "Application" in the last line of code to "WorksheetFunction" and see if that may work for you.