Swap cells on Sheet 1 to swap columns on Sheet 2

Naveed7991

New Member
Joined
Nov 28, 2017
Messages
2
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>
A
BCD
1
2
34
87
6
5
901132

<colgroup><col style="width:65pt" span="4" width="87"> </colgroup><tbody>
</tbody>


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>
A
B
C
D

<colgroup><col style="width:65pt" width="87"> </colgroup><tbody>
</tbody>

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>
A
D
C
B

<colgroup><col style="width:65pt" width="87"> </colgroup><tbody>
</tbody>

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>
A
D
CB
1432
8567
932110

<colgroup><col style="width:65pt" span="4" width="87"> </colgroup><tbody>
</tbody>

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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Give this macro a try...
Code:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top