Swap cells on Sheet 1 to swap columns on Sheet 2

Naveed7991

New Member
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.
 

Rick Rothstein

MrExcel MVP
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:

Naveed7991

New Member
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

MrExcel MVP
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top