Having and issue with the Find command in my macro

[kittysaid]

I am using Windows 7, and Excel 2010

I regularly need to take some CMM data that comes out of our system and then only copy the characteristics that we need to analyze
I copy the data to the spreadsheet onto a sheet called "EDIT DATA" starting at B1.
I then my macro I created using the macro recorder.
The macro copies the name of the column headings that are listed on Column A one at a time and using the find command, looks for that column. It then then cuts the whole column of data and pastes in in the correct order onto the next sheet.
Column A in sheet EDIT DATA is the list of characteristics I want to find and it is in the proper order.

The issue is when importing the data, it is often in a different order, and not always in the same columns when I download it from the system.
So when I run the macro, it cuts and pastes the wrong columns. So even though I use the find command, the macro just cuts the same column each time it is run.

Primary Goal:
1) To always find these named columns no matter where they are in the data.
Secondary Goal:
To make the macro work with minor editing in other spread sheets with different quantity and different names in column A.
Example being, replacing column A with these names:

I have posted in Box.net an example file in the link below and it includes more detail on my issue.

https://app.box.com/s/jy98x0xl05ofy27uxwdksts73lwjatwl

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I have had a look at your macro. It is basically a good idea but, because it has been recorded, Excel gives you no help in making it general. Everything you used is hard-coded in the macro.

I hope this is not going to be too big a change, but I have taken the basic operation from your macro and put it inside a loop.

The basic operation, it seems to me, is get the column name from column A of the input sheet;
Find the column that has that data in it;
Cut and paste that data to the next available column in the output sheet.

There are some differences. I don't cut and paste I copy the data because it is quicker and I have not moved the first lump of data in a block. I process each column in turn. This should help if you want to move the macro elsewhere some time later because it makes it more general. I also clear the output sheet before I start with the copy. (That makes testing easier, for one thing but should also be more robust in operation.)

The whole thing now looks like this:

Sub SORT_SC()

    Dim wsIn As Worksheet: Set wsIn = Worksheets("Edit Data")
    Dim wsOut As Worksheet: Set wsOut = Worksheets("SC Only Data")
    Dim lr As Long, iColIn As Long, iColOut As Long, i As Long
    
    'Temporarily disable screen updating
    Application.ScreenUpdating = False
    
    ' Empty the output worksheet
    wsOut.Cells.Clear
    
    With wsIn
        ' Find the last data row
        lr = .Cells(Rows.Count, "B").End(xlUp).Row

        ' Initialize the first output column
        iColOut = 1
        
        ' Loop round the column headings in column A
        For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
            ' Find the matching data column
            iColIn = .Rows(1).Find(What:=.Cells(i, "A").Value, After:=.Range("A1"), LookIn:=xlFormulas, _
                        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False).Column
            ' Copy the column to the output sheet
            wsOut.Range(wsOut.Cells(1, iColOut), wsOut.Cells(lr, iColOut)).Value = wsIn.Range(.Cells(1, iColIn), .Cells(lr, iColIn)).Value
            ' Increment the output column for the next time round the loop
            iColOut = iColOut + 1
        Next
    End With
    
    ' Re-enable screen updating
    Application.ScreenUpdating = True
End Sub

All the real work is done inside the For...Next loop.
Instead of the Find string being hardcoded it is now: .Cells(i, "A").Value
In other words, the value in row number i in column A. i changes from 1 to the maximum number of columns in column A of worksheet Edit Data.
The FIND statement now returns the column number which is used later.

 

[kittysaid]

Thanks so much RickXL. I will try it next week.

 

[RickXL]

No problem.

It may seem a bit complicated at first but if you need something explaining please come back.

Have a good weekend.