<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Hi Everyone,
I have a macro that deletes unnecessary columns and rearranges the remaining columns perfectly for the active sheet, but there are three other sheets that have the same headers, but different data. My goal is to have the macro delete and rearrange the columns on each sheet. I'm stuck and it's starting to make me go crazy. Please help!
</code>
I have a macro that deletes unnecessary columns and rearranges the remaining columns perfectly for the active sheet, but there are three other sheets that have the same headers, but different data. My goal is to have the macro delete and rearrange the columns on each sheet. I'm stuck and it's starting to make me go crazy. Please help!
Code:
Sub DDO_BMG()
'
' DDO_BMG Macro
'
Dim aCell As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Sheets
With ws
For Each aCell In .UsedRange
If Not aCell.Value = "" And aCell.HasFormula = False Then
With aCell
.Value = Replace(.Value, Chr(160), "")
.Value = Application.WorksheetFunction.Clean(.Value)
.Value = Trim(.Value)
End With
End If
Next aCell
End With
Next ws
For Each ws In Sheets
On Error Resume Next
ws.Cells(1, 13).EntireColumn.Delete
ws.Cells(1, 12).EntireColumn.Delete
ws.Cells(1, 9).EntireColumn.Delete
ws.Cells(1, 1).EntireColumn.Delete
ws.Cells(1, 10).Value = "STATUS"
ws.Cells(1, 11).Value = "USER RESPONSE"
ws.Cells(1, 12).Value = "COMMENTS"
ws.Cells.EntireColumn.AutoFit
Next ws
Dim v As Variant, x As Variant, findfield As Variant
Dim oCell As Range
Dim iNum As Long
v = Array("UserName", "FIRST_NAME", "LAST_NAME", "EMAIL_ID", "AU_NAME", "DatabaseName", "TVMName", "LogDate", "access_cnt", "STATUS", "USER RESPONSE", "COMMENTS")
For x = LBound(v) To UBound(v)
findfield = v(x)
iNum = iNum + 1
Set oCell = ActiveSheet.Rows(1).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not oCell.Column = iNum Then
Columns(oCell.Column).Cut
Columns(iNum).Insert Shift:=xlToRight
End If
Next x
Columns(9).NumberFormat = "0"
ws.Cells.EntireColumn.AutoFit
End Sub