Variable problem (2000)

[Steve_in_Kent]

Two part tuesday morning problem...


<font color=blue>sTest = ThisWorkbook.Worksheets("Line 2").Range("S14").Value</font color=blue>

is my code, i've been playing with it for ages, and can't find how to introduce a variable in to the equation, eg:

<font color=blue>MyLoop = 14
sTest = ThisWorkbook.Worksheets("Line 2").Range("S & MyLoop & ").Value</font color=blue>

I've tried all manner of speech mark combinations but still can't get it.



Secondly, is there a way to make this line of code, copy the value, and not the formula in the cell.

<font color=blue>Sheets("Line 2").Range("A14:Q14").Select

Selection.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Email").Range("A10")</font color=blue>

[HansV]

1. The "S" is fixed, so that must be in quotes; the MyLoop part is variable, so that must be outside the quotes. Use

sTest = ThisWorkbook.Worksheets("Line 2").Range("S" & MyLoop).Value

2. You must use PasteSpecial for this; this means you must use 2 statements:

Worksheets("Line 2").Range("A14:Q14").SpecialCells(xlCellTypeVisibl e).Copy
Worksheets("Email").Range("A10").PasteSpecial Paste:=xlPasteValues

[Steve_in_Kent]

Thanks Hans, as usual you have the answer, and i'm another small step in the path to enlightenment !

You know too much ! <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/artist.gif border=0 alt=artist width=34 height=29> <img src=/S/clown.gif border=0 alt=clown width=15 height=15> <img src=/S/cool.gif border=0 alt=cool width=15 height=15> <img src=/S/hello.gif border=0 alt=hello width=25 height=29>