Results 1 to 4 of 4

Thread: Swap cells on Sheet 1 to swap columns on Sheet 2
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Swap cells on Sheet 1 to swap columns on Sheet 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

    A
    B C D
    1 2 3 4
    8 7 6 5
    9 0 11 32


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

    A
    B
    C
    D

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

    A
    D
    C
    B

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

    A
    D
    C B
    1 4 3 2
    8 5 6 7
    9 32 11 0

    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.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,317
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Swap cells on Sheet 1 to swap columns on Sheet 2

    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 by Rick Rothstein; Nov 28th, 2017 at 07:04 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Nov 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Swap cells on Sheet 1 to swap columns on Sheet 2

    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.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,317
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Swap cells on Sheet 1 to swap columns on Sheet 2

    Quote Originally Posted by Naveed7991 View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •