Column Search Error

[msnook]

I am using VBA to look through a set of data that is 17 columns by 12000 rows. It finds a specific column header copies the column and places it onto a secondary sheet. I do this because the data report I recieve isn't always in same order and often has more data than I need. I have included the coding used. The code goes to the Heading List works through the list one by one pulling data from the Raw Data page and placing it on the Output page. I only have one problem when I come to the column named Service. The Macro wont always find this column and places a different one there yet sometimes it does find it. The info that it pulls in the instances it can not find the correct column varies. Thanks in advance and any help is greatly appreciated.

Dim LastColumn As Integer

For x = 1 To 20
Sheets("Heading List").Select
MyDat = Sheets("Heading List").Range("D" & x)

If (MyDat <> "") Then

Sheets("Raw Data").Select

If WorksheetFunction.CountA(Cells) > 0 Then
LastColumn = Cells.Find(What:=MyDat, After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If

Columns(LastColumn).Select
Selection.Copy
Sheets("Output").Select

If WorksheetFunction.CountA(Cells) > 0 Then
LastColumn = Cells.Find(What:="", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If

Columns(x).Select
ActiveSheet.Paste
Columns(x).EntireColumn.AutoFit

End If

Sheets("Heading List").Select

Next x

 

[SkipVought]

hi,

I do this because the data report I recieve isn't always in same order

If you were to use MS Query, then the column order is irrelevant. Also no VBA would be required.

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[msnook]

I need to create a page so that I can load the data into an Access database.

 

[SkipVought]

And...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}