Hi,

I have two worksheets, LETFILE and LETconsol, LETFILE is a data file that I create
a template for user to input data and LETconsol is a collect data file that I collect
data from many LETFILE(1,2,3....). The problem is I cannot store text from LETFILE cell E5 to variable and paste it to LETconsol. I create this text as a drop down list
from data validation for users to select which option do they need to 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 result 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 is 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
Loop