Trouble Copying Columns from One Sheet to Another Using VBA

[monicamarie]

Hello.
I have a worksheet (Sheet1) with 8 columns of data starting on Row 1. The column names are Cust Number, Created On, Account, Problem, State, ST, System Type and Ticket. (Not always in that order)
The code below is used to copy specific columns from sheet 1 to a new created worksheet called Service Queue.
The problem is that for some reason, it's not copying the column named "ST" from Sheet 1 but instead it's copying a column called "State" Can someone help me to fix this issue?

Sub MoveColumns()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Integer

Sheets.Add.Name = "Service Queue"
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Service Queue")
myColumns = Array("Cust Number", "Created On", "ST")


With ws1.Range("A1:H1")
For i = 0 To UBound(myColumns)
On Error Resume Next
.Find(myColumns(i)).EntireColumn.Copy Destination:=ws2.Cells(1, i + 1)
Err.Clear
Next i
End With
Set ws1 = Nothing
Set ws2 = Nothing


End Sub

 

[MARK858]

That's what happens when you don't use the various paremeters of Find I'm afraid (it will use whatever it's previous settings were).
Try...

.Find(What:=myColumns(i), LookAt:=xlWhole).EntireColumn.Copy Destination:=ws2.Cells(1, i + 1)

 

[monicamarie]

Hi Mark - I can't thank you enough. This worked perfectly!

 

[MARK858]

You're welcome