Hi,

I have two worksheets, LETFILE and LETconsol, LETFILE is a data fil
that I create
a template for user to input data and LETconsol is a collect data fil
that I collect
data from many LETFILE(1,2,3....). The problem is I cannot store tex
from LETFILE cell E5 to variable and paste it to LETconsol. I creat
this text as a drop down list
from data validation for users to select which option do they need t
put into this
cell. Could you please help me how to fix it ? Thank you.



<<<<<< Here is my code


Worksheets("LET").Activate


On Error Resume Next
Set Letsheet = ThisWorkbook.Sheets("LET")
Row = 5 'LET Row
iRow = 2 'LETconsol Row
Do Until IsEmpty(Letsheet.Cells(Row, 2))
Windows(LETFILE).Activate
Worksheets("LET").Activate
ChangeLosstype = Letsheet.Range(Row, 5).Formula

_'at_this_point_I_try_to_change_many_properties_eg ._Letsheet.Rnge(Row,5).Text_or
'_.Value______...............It_doesn't_work_the_r esult_is_empty_or_it_returns_\"_\"._
x = MsgBox(ChangeLosstype)
Select Case ChangeLosstype
Case ChangeLosstype = "Option 1"
Losstype = 1
Case ChangeLosstype = "Option 2"
Losstype = 2
Case ChangeLosstype = "Option 3"
Losstype = 3
Case Else
x = MsgBox("An Error Occur ...There Loss Type i
not correct.....Please recheck")
End Select

'Start at Select Range C5:D5 from "LET" worksheet
Range(Cells(Row, 3), Cells(Row, 4)).Select
Selection.Copy

'Switch to LETconsol.xls
Windows("LETconsol.xls").Activate
Sheets("test").Select
'Start Select Range B2 from "LETconsol" worksheet
Range(Cells(iRow, 2)).Select
Selection.PasteSpecial Paste:=xlPasteValues
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range(Cells(iRow, 4)).Select


'paste text variable into cell
ActiveCell.Formula = Losstype
iRow = iRow + 1
Row = Row + 1
'End If
Loo

--
jafer

-----------------------------------------------------------------------
jafery's Profile: http://www.excelforum.com/member.php...fo&userid=2764
View this thread: http://www.excelforum.com/showthread.php?threadid=47169