Column lookup macro

Hello Anna,

See if this heads you in the right direction:-

Option Explicit


Sub Test()


Dim ar As Variant


Sheet1.UsedRange.ClearContents


With Sheet2.[B3].CurrentRegion
ar = Application.Index(.Value, Evaluate("Row(1:" & .Rows.Count & ")"), Array(1, 2, 3, 10, 11, 12, 4, 5, 6, 7, 8))
End With


Sheet1.[B4].Resize(UBound(ar), 11) = ar
Sheet1.Columns.AutoFit


End Sub

I've attached your sample workbook with the code implemented. The "Output" sheet is empty. Click on the "TEST ME" button to see how it works.

I hope that this helps.

Cheerio,

vcoolio.

Anna.xlsm

Anna,

You do not need VBA to do this. You can sort from left to right based upon row 3. On the Data tab, select the sort Icon and select sort from left to right

Hello Anna,

Column 9 is not included because, in your sample output, you have not included it (Coco).

If you need it to be included, add 9 after the 8 in the array and change the resize value to 12.

The array appears random but is in fact quite precise. You have asked that columns K, L and M (10, 11 &12 based on your data setout) in the Input sheet be placed in columns E, F and G in the Output sheet starting at B4. The positions of the columns in the array determines the positions in the Output sheet.

I don't quite follow your last request so, seeing that your supplied sample is not truly indicative of your actual workbook and its data, please supply a new sample set out exactly the same as your actual workbook including the data within the columns (ensure that it is still dummy data).

Cheerio,

vcoolio.