Rearranging columns user defined

[Anand28]

Dear excel heroes,

I'm trying to create a vba macro which gives an output sheet which shows me the columns in the order that I want for instance:

Input sheet:

Column A = Make
Column B = Model
Column C -> Empty column
Column D = License Plate number
Column E = Tax
Column F -> Empty column
Column G = Weight

Output Order:

Column A = License Plate number
Column B = Make
Column C = Model
Column D = Weight
Column E = Tax




Can anybody help me?

I've found the macro mentioned below, only this stops when one of the header columns is empty

Sub Rapport_generator()

   For A = 1 To 50
      fieldname = Sheets("Header_order").Cells(1, A)
    
      If fieldname = "" Then Exit Sub
    
      On Error GoTo Errhandler

    Application.ScreenUpdating = False

    Sheets("Input_Sheet").Select
    Rows("2:2").Select
  
      
    Cells.Find(What:=Sheets("Header_order").Cells(1, A), After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
  
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Copy
  
    Application.ScreenUpdating = True
  
    Sheets("Output_Sheet).Select
    ActiveCell.Columns("A:A").EntireColumn.Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
  
  
       Next A

    'Application.ScreenUpdating = True

Errhandler:

      Select Case Err

         Case 68, 75:    ' Error 68: "Device not available"
                         ' Error 75: "Path/File Access Error"
            MsgBox "There is an error reading drive B."

         Case 91:         'Error 91: "Path not found"
          
                     errmsg = "Veldnaam " & veldnaam & _
                     " not found. Choose OK next field"
                  
                     Result = MsgBox(errmsg, vbOKCancel)
                
                     If Result = vbOK Then Resume Next
                                      
         Case Else:      ' An error other than 68, 75 or 76 has occurred.
           '  Display the error number and the error text.
            MsgBox "Error # " & Err & " : " & Error(Err)

      End Select

End Sub

Kind regards,

Anand

 

[Marc L]

Hi !

First, according to forum rules, use code tags !

See for an advanced filter (Excel function), needs less than 20 codelines …
Or even with simple Range.Copy method …
​

 

[Anand28]

Hi Marc L,

Sorry about that I'll do according the rules. Do I need to change it before I go further?

Could you give an sample with the Range.Copy?

Any help is really welcome

 

[Asheesh]

Check the below link..it should help..

http://chandoo.org/forum/threads/re...ames-vba-codes-using-array.24537/#post-147650

 

[Marc L]

Could you give an sample with the Range.Copy?

As written in inner VBA help, easy after read it !​

   Sheet1.Cells(1).CurrentRegion.Copy Sheet2.Cells(2)

 

[Anand28]

Thank you Asheesh and Marc L. Your help is most welcome Your answers gives thinks to consider