Copy data from a range in a workbook based on a value in another

[DJ Dusty]

Hello,

I need to be able to copy a range of data from one workbook which is
determined by the contents of a cell in another.

For example:

Workbook A contains the following data:


.. A B C
1 X Y Z
2 X Y Z
3 X Y Z


Workbook B contains a text entry "B" in cell A1.

Workbook C will be used to paste data in.

I would like to copy the column from Workbook A based on the contents
of A1 in Workbook B and paste them into Workbook C.

In the example above, because "B" is in cell A1 of Workbook B, then it
is column B from Workbook A that must be copied. Similarly, if Workbook
B's cell A1 contained "C", then I want to copy column C, etc.

I've wracked my brains for a while on this one, and keep going round in
circles. If anyone could provide the solution, if just for my own
sanity?

Thanks!

 

[Dick Kusleika]

DJ

Psuedo code:

Dim lCol As Long

lCol = Asc(WbB.Sheets(1).Range("A1").Value) - Asc("A") + 1

WbA.Sheets(1).Columns(lCol).Copy
WbC.Sheets(1).Columns(lCol).Paste

Asc() returns the ascii number of a letter. Asc("A") returns 65, Asc("B")
returns 66 and so on.