VBA To Move Column Based On Value In Row 1
Results 1 to 8 of 8

Thread: VBA To Move Column Based On Value In Row 1

  1. #1
    Board Regular
    Join Date
    Nov 2012
    Posts
    224
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA To Move Column Based On Value In Row 1

    Hi all,

    I am looking for some help, I am trying to create code that will move a column of data to another column location on the same worksheet based on the value found in row 1

    On my report I have the following headers in row 1 plus many more however these are the important ones
    Supplier (A)
    Name (B)
    Order (C)
    Item Number (D)
    Receipt Quantity (E)
    Quantity Open (F)
    Status (G)
    Receipt Date (H)
    Performance Date (I)
    Due Date (J)
    Need Date (K)

    The issue I have is that these can appear anywhere on the extracted spreadsheet as there locations are user defined and I need several people to be able to produce the data required, next to each of the above there is a the letter for where I need these columns to be moved to. Once they have been moved everything after these columns can be deleted so if this could be part of the above it would be appreciated.

    Obviously if anyone can provide the code for one of these and then I only need to replicate the detail for the others only change the search criteria and end location.

    Many thanks

  2. #2
    Board Regular
    Join Date
    Nov 2012
    Posts
    224
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Move Column Based On Value In Row 1

    I've only gone and done it, well the sort anyway - see below

    Code:
    Sub columnOrder()Dim search As Range
    Dim cnt As Integer
    Dim colOrdr As Variant
    Dim indx As Integer
    
    
    Application.ScreenUpdating = False
    
    
    colOrdr = Array("Supplier", "Name", "Order", "Item Number", "Receipt Quantity", "Quantity Open", "Status", "Receipt Date", "Performance Date", "Due Date", "Need Date")
    
    
    cnt = 1
    
    
    
    
    For indx = LBound(colOrdr) To UBound(colOrdr)
        Set search = Rows("1:1").Find(colOrdr(indx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        If Not search Is Nothing Then
            If search.Column <> cnt Then
                search.EntireColumn.Cut
                Columns(cnt).Insert Shift:=xlToRight
                Application.CutCopyMode = False
            End If
        cnt = cnt + 1
        End If
    Next indx
    
    
    Application.ScreenUpdating = True
    
    
    End Sub
    Quote Originally Posted by TkdKidSnake View Post
    Hi all,

    I am looking for some help, I am trying to create code that will move a column of data to another column location on the same worksheet based on the value found in row 1

    On my report I have the following headers in row 1 plus many more however these are the important ones
    Supplier (A)
    Name (B)
    Order (C)
    Item Number (D)
    Receipt Quantity (E)
    Quantity Open (F)
    Status (G)
    Receipt Date (H)
    Performance Date (I)
    Due Date (J)
    Need Date (K)

    The issue I have is that these can appear anywhere on the extracted spreadsheet as there locations are user defined and I need several people to be able to produce the data required, next to each of the above there is a the letter for where I need these columns to be moved to. Once they have been moved everything after these columns can be deleted so if this could be part of the above it would be appreciated.

    Obviously if anyone can provide the code for one of these and then I only need to replicate the detail for the others only change the search criteria and end location.

    Many thanks

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: VBA To Move Column Based On Value In Row 1

    I think my code is pretty close to yours anyway, but here is what I was developing.
    Test in a copy of your workbook.

    Code:
    Sub Rearrange_Columns()
      Dim myCols As Variant
      Dim i As Long, c As Long, k As Long
      
      myCols = Split("Supplier,Name,Order,Item Number,Receipt Quantity,Quantity Open,Status,Receipt Date,Performance Date,Due Date,Need Date", ",")
      Application.ScreenUpdating = False
      For i = UBound(myCols) To 0 Step -1
        c = 0
        On Error Resume Next
        c = Rows(1).Find(What:=myCols(i), LookAt:=xlWhole, MatchCase:=False).Column
        On Error GoTo 0
        If c > 0 Then
          Columns(c).Cut
          Columns(1).Insert
          k = k + 1
        End If
      Next i
      ActiveSheet.UsedRange.Offset(, k).EntireColumn.Delete
      Application.ScreenUpdating = True
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Nov 2012
    Posts
    224
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Move Column Based On Value In Row 1

    Yours also works, I think I'll use this as seems to be a little quicker - thank you.

    Quote Originally Posted by Peter_SSs View Post
    I think my code is pretty close to yours anyway, but here is what I was developing.
    Test in a copy of your workbook.

    Code:
    Sub Rearrange_Columns()
      Dim myCols As Variant
      Dim i As Long, c As Long, k As Long
      
      myCols = Split("Supplier,Name,Order,Item Number,Receipt Quantity,Quantity Open,Status,Receipt Date,Performance Date,Due Date,Need Date", ",")
      Application.ScreenUpdating = False
      For i = UBound(myCols) To 0 Step -1
        c = 0
        On Error Resume Next
        c = Rows(1).Find(What:=myCols(i), LookAt:=xlWhole, MatchCase:=False).Column
        On Error GoTo 0
        If c > 0 Then
          Columns(c).Cut
          Columns(1).Insert
          k = k + 1
        End If
      Next i
      ActiveSheet.UsedRange.Offset(, k).EntireColumn.Delete
      Application.ScreenUpdating = True
    End Sub

  5. #5
    Board Regular
    Join Date
    Nov 2012
    Posts
    224
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Move Column Based On Value In Row 1

    Sorted the delete function as well

    Code:
    Dim luc As Long
    With ActiveSheet
    luc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
    If luc > 11 Then
        .Columns(12).Resize(, luc - 12 + 1).Delete
    End If
    End With
    Quote Originally Posted by TkdKidSnake View Post
    Hi all,

    I am looking for some help, I am trying to create code that will move a column of data to another column location on the same worksheet based on the value found in row 1

    On my report I have the following headers in row 1 plus many more however these are the important ones
    Supplier (A)
    Name (B)
    Order (C)
    Item Number (D)
    Receipt Quantity (E)
    Quantity Open (F)
    Status (G)
    Receipt Date (H)
    Performance Date (I)
    Due Date (J)
    Need Date (K)

    The issue I have is that these can appear anywhere on the extracted spreadsheet as there locations are user defined and I need several people to be able to produce the data required, next to each of the above there is a the letter for where I need these columns to be moved to. Once they have been moved everything after these columns can be deleted so if this could be part of the above it would be appreciated.

    Obviously if anyone can provide the code for one of these and then I only need to replicate the detail for the others only change the search criteria and end location.

    Many thanks
    Last edited by TkdKidSnake; Aug 3rd, 2019 at 07:42 AM.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: VBA To Move Column Based On Value In Row 1

    Quote Originally Posted by TkdKidSnake View Post
    Sorted the delete function as well

    Code:
    Dim luc As Long
    With ActiveSheet
    luc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
    If luc > 11 Then
        .Columns(12).Resize(, luc - 12 + 1).Delete
    End If
    End With
    Mine already had the delete in it. Didn't it do what you wanted?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Nov 2012
    Posts
    224
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA To Move Column Based On Value In Row 1

    Yes but I had already sorted the code to delete so if someone wanted just this thought it would be worthwhile adding it.

    Thank you for your help.

    Quote Originally Posted by Peter_SSs View Post
    Mine already had the delete in it. Didn't it do what you wanted?

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: VBA To Move Column Based On Value In Row 1

    OK, no problem.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •