VBA to sort columns

[alanlambden]

Hi All,

I have multiple tabs I need to merge. I have been able to write the code to merge the into data into one spreadsheet, but sometimes that columns aren't a match. Is there a way I can sweep some VBA code over my workbook and sort the columns depending on the title, ideally going through all the tabs in one run.

Say the tabs are labelled 'Tab1', 'tab2' 'tab3' .... 'tab50'

Here is the example .. Is there a way I can check each tab and identify

if column heading 'blue' exists, move this to columnC.
If column heading 'yellow' exists, move this to columnD and
if column heading 'red' exists, move this to columnE

and so on. I have about 9 columns I need to ensure are all in the same corresponding column.

Also some tabs are missing some of the headings, like 'red' might be missing so:

if column heading 'red' doesn't exist, ignore and move onto the next column and leave it blank.

This would get every column in the correct place, allowing me to run the macro to collate each tab into a master tab.

Thanks for your help with this.

 

[DanteAmor]

In my tests it works well, check that the headers have the same name without spaces.
Perform a test with 2 sheets.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[alanlambden]

https://www.dropbox.com/s/7e0i4yphl79dj3d/MyVBA_mrexcel.xlsx?dl=0

Here is a trimmed down version of my file. There's four worksheets (but in fact there's meant to be 93). You will notice that the headings are different for each tab. Column 1 and 2 - 'Hello' and 'depth' are to stay where they are. The rest of the columns, whether they are present or not, should take the order:

("Caliper", "VSP", "Temp", "GR", "N8", "N16", "N32", "N64", "Cond", "Velocity")

The first three tabs are real data and to be sorted by the code. The fourth tab is what tab 3 should look like after the VBA runs.

Tabs 1, 2, 3 are mixed up, not taking the above order. I want to sort them in the specific order as above. Things to note:

Planilha 1 and Planilha1 (2) are mixed up and need to be sorted in specified order.
Planilha1 (3) is a little more complicated. It is mixed up but also missing two headings "Temp" and "Velocity". These columns must be occupied but with blank cells. The fourth tab "Result of planilha1 (3)" is what "planilha1 (3)" should look like after the code runs.

Hope this helps in some way and sorry if I missed crucial information in my earlier explanations.

 

[DanteAmor]

You didn't check the headings.
My comment on post #11

check that the headers have the same name without spaces.

If you don't want to delete the spaces, then use the following macro.

Adjust the headings and columns as desired.

Sub sort_columns()
  Dim sh As Worksheet, lbls As Variant, cols As Variant
  Dim cold As Long, i As Long, f As Range, colo  As Long
  '
  Application.ScreenUpdating = False
[COLOR=#0000ff]  lbls = Array("VSP", "Temp", "GR", "N8", "N16", "N32", "N64", "Cond", "Velocity")[/COLOR]
[COLOR=#0000ff]  cols = Array("C", "D", "E", "F", "G", "H", "I", "J", "K")[/COLOR]
  For Each sh In Sheets
    Select Case True
      Case Left(sh.Name, 8) = "Planilha"
        For i = 0 To UBound(lbls)
          Set f = sh.Rows(1).Find(lbls(i), , xlValues, [COLOR=#ff0000][B]xlPart[/B][/COLOR])
          If Not f Is Nothing Then
            sh.Columns(f.Column).Cut
            cold = Range(cols(i) & 1).Column
            colo = f.Column
            If colo < cold Then
              cold = cold + 1
              sh.Columns(cold).Insert Shift:=xlToRight
            ElseIf colo > cold Then
              sh.Columns(cold).Insert Shift:=xlToRight
            End If
          End If
        Next
    End Select
  Next
  Application.CutCopyMode = False
End Sub

 

[alanlambden]

Oh boy. That worked!

Things piled up on me this week and id kind of thrown in the towel with this task.

Thanks so much for the time you've dedicated to this, I cant get over how helpful people are on this forum. Gold star Dante Amor. Top player.

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.

 

[alanlambden]

Hi Dante,

Is there a way to adjust the code so the names of the tabs are ignored? If i run my code on another worksheet where the tabs are not named "planilha", nor are they numbered sequentially, is there a way to just process each tab regardless of name?

 

[DanteAmor]

Try this

Pay attention to details!

Sub sort_columns()
  Dim sh As Worksheet, lbls As Variant, cols As Variant
  Dim cold As Long, i As Long, f As Range, colo  As Long
  '
  Application.ScreenUpdating = False
  lbls = Array("VSP", "Temp", "GR", "N8", "N16", "N32", "N64", "Cond", "Velocity")
  cols = Array("C", "D", "E", "F", "G", "H", "I", "J", "K")
  For Each sh In Sheets
    Select Case sh.name
      Case "Master", "Summary", "etc"  [COLOR=#ff0000]'Put here the names of the sheets you [B]don't [/B]want to process[/COLOR]
      Case Else
        For i = 0 To UBound(lbls)
          Set f = sh.Rows(1).Find(lbls(i), , xlValues, xlPart)
          If Not f Is Nothing Then
            sh.Columns(f.Column).Cut
            cold = Range(cols(i) & 1).Column
            colo = f.Column
            If colo < cold Then
              cold = cold + 1
              sh.Columns(cold).Insert Shift:=xlToRight
            ElseIf colo > cold Then
              sh.Columns(cold).Insert Shift:=xlToRight
            End If
          End If
        Next
    End Select
  Next
  Application.CutCopyMode = False
End Sub

 

[alanlambden]

great work!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.