Column reordering with VBA

[Jimbob2000]

I have the following code as part of a macro to reformat a spreadsheet when transferring information between two databases at a children's nonprofit where I work.

This part of the code is supposed to reorder the columns, and it works.... about half the time.

When it doesn't work, the debug function always highlights the row "Columns(counter).Insert Shift:=xlToRight" and it seems to get as far as moving the "Zip Code" column, but I don't understand this bit of code well enough to see what's going wrong. It always crashes excel when it doesn't work, which sucks.

Any help or suggestions would be gratefully received.

Thanks

Dim ColumnOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer
    ColumnOrder = Array("Custom Case ID", "Referral Date", "Last Name", "First Name", "Zip Code", "Locator Info")
counter = 1
  
For ndx = LBound(ColumnOrder) To UBound(ColumnOrder)
    Set Found = Rows("1:1").Find(ColumnOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not Found Is Nothing Then
        If Found.Column <> counter Then
            Found.EntireColumn.Cut
            Columns(counter).Insert Shift:=xlToRight
            Application.CutCopyMode = False
        End If
    counter = counter + 1
    End If
Next ndx

 

[JoeMo]

What is the value of counter when you get the error?

 

[Jimbob2000]

Hi Joe - For some reason, I haven't been able to get it to not work for a bit, but when it doesn't work, it seems to be on the ZIP code column, so I guess that would be count = 5 (?)

 

[JoeMo]

Hi Joe - For some reason, I haven't been able to get it to not work for a bit, but when it doesn't work, it seems to be on the ZIP code column, so I guess that would be count = 5 (?)

If Zip Code happens to be in the very last column (col number 16,384), the code will encounter an error at this line:
Columns(counter).Insert Shift:=xlToRight
because you can't shift a column off the sheet as shift:= xlToRight demands.

Is it possible that occasionally Zip Code might be in the last column (column letters XFD)?