Sub EFG()
Dim rng As Range, bReplace As Boolean
' this puts in formulas. If you want them
' replaced with the values they return, then
' set the value of bReplace to True

bReplace = False

With Worksheets("Time")
Set rng = Range("C7", Range("C7").End(xlDown))
End With

rng.Offset(0, 4).Formula = "=INDIRECT(""'""&C7&""'!J43"")"
rng.Offset(0, 5).Formula = "=INDIRECT(""'""&C7&""'!L43"")"

If bReplace Then
With rng.Offset(0, 4).Resize(, 2)
.Formula = .Value
End With
End If
End Sub

--
regards,
Tom Ogilvy

" wrote:

Hello All,
I am using Office 2003.
I have a workbook with 30+ Worksheets plus a Worksheet named TIME.

For Example:
Sheet - TIME has name ABC, DEF, GHI (not necessary in this order) in
Column C7 downwards
Other Sheets name ABC, DEF, GHI ... ... (these names are only for
explanatory purposes)

I wish to have a macro which when run should do the following on the
Sheet named TIME.

Look Up the Value in Column C of Sheet named TIME and Copy the value
of J:43 of respective sheets in Column G and the value of L:43 of
respective sheets in Column H. i.e value of J:43 of ABC Sheet should
be copied to TIME sheet against ABC in Column G and value of L:43 of
ABC Sheet should be copied to TIME sheet against ABC in Column H... and
for the rest of the sheets.

I hope I am clear

Any help would be greatly appreciated.

TIA

Rashid Khan