Referencing Sheet Names

[Stuart Grant]

It seems so elementary I'm almost ashamed to ask.
I want to copy the ActiveCell.Value to a cell on another sheet in the same
workbook. The sheet is called Q1Trans. I put in the macro
ActiveCell.Copy Destination:=Q1Trans!Range("C101")
At Q1Trans in the macro I get the error message "Variable not defined". I
have tried putting it in parentheses with and without single or double
quotes but again get an error message.
If I substitute Sheet6 for Q1Trans, the macro works.
Why won't it accept the Sheet name as shown on the Tab ?
Stuart

 

[JE McGimpsey]

Try:

ActiveCell.Copy Destination:=Worksheets("Q1Trans").Range("C101")

or, somewhat less efficiently (IIRC):

Activecell.Copy Destination:=Range("Q1Trans!C101")