Running a mcro based on value of a cell

A

ADE2

I have a spreadsheet that performs End Of Day calculations on Financia
Market instruments.

I have a large macro that runs at the end of every day,part way throug
the macro i need to collect data from a column in a worksheet based o
the text that appears in cell T1.

In the YEARLY sheet i have columns of data that have the end of yea
prices of various Financial instruments.

In row 1 across the worksheet are the names,e.g Vodafone,Do
Jones,Nasdaq,GBP/USD etc.Below each name is a column of 20 year
data,i.e A2:A21,B2:B21,C2:C21 etc.

The macro needs to look at the text in T1 and then find the appropriat
column of data in the YEARLY sheet, copy and then paste into th
CALCULATIONS sheet.


So if cell T1 said VODAFONE,look at row one in the YEARLY sheet to fin
VODAFONE(C1) then copy values in cells C2:C21 and paste int
CALCULATIONS sheet .

Thanks for any ideas

Ad
 
A

ADE2

Dim Col As Integer
With Worksheets("YEARLY")
Col = WorksheetFunction.Match(Worksheets("YourSheet").Range("T1")
_
.Range("A1"
.Range("A1").End(xlToRight)), _
0)
.Range(.Cells(2, Col), .Cells(21, Col)).Copy _
Destination:=Worksheets("CALCULATIONS").Range("A1")
End With


The values in red need replacing with your own values (i.e. the shee
that cell T1 is on, and the destination cell on the calculation
sheet
 
Top