Hi, this code was doing its job fine over the past 2 days. I didn't change anything. Now all of the sudden I keep getting this error. I've tried to find a solution to people with similar problem but it hasn't been successful....Please help!
Sub Match_And_Rearrange_Columns()
Dim counter As Long, Found As Range, Cell As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Main") 'Match column headers on this sheet
Set ws2 = Sheets("New Data") 'Rearrange columns on this sheet
Application.ScreenUpdating = False
counter = 1
Application.CutCopyMode = False
Application.FindFormat.Font.FontStyle = "Bold" 'match the format
For Each Cell In ws1.Range("A1", ws1.Cells(1, Columns.Count).End(xlToLeft))
Set Found = ws2.Rows(1).Find(What:=Cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=True, _
SearchFormat:=True)
If Not Found Is Nothing Then
If Found.column <> counter Then
Found.EntireColumn.Cut
ws2.Columns(counter).Insert Shift:=xlToRight '<----automation error!!!
Application.CutCopyMode = False
End If
Else
ws2.Columns(counter).Insert Shift:=xlToRight
ws2.Columns(counter).ColumnWidth = Cell.ColumnWidth
ws2.Cells(1, counter).Value = Cell.Value
End If
counter = counter + 1
Next Cell
Application.ScreenUpdating = True
End Sub
Bookmarks