Worksheet reference vs Worksheet("").Activate

[DoctorG]

Is there a way to avoid changing back and forth between Worksheets in order
to execute a certain command in another work area (Worksheet)?

Is there something that refers to a Worksheet which, if placed before an
ActiveCell statement as is the case below, will refer to the ActiveCell of
another Worksheet and not the current one???? I remember from my Clipper days
that we could precede any function with a Workarea reference and the command
would first switch to the specified workarea, execute and then return to the
current workarea by itself.

Example
----------
Case : Move records of a specific invoice from a database to another
worksheet for reporting. Locate an invoice, "walk" through it moving its
items to the Report Worksheet one by one, and then move on...

.....
Worksheet("Data").Activate
loadLine = 1
While Left(ActiveCell.Value, workKeyLen) = workKey

work1 = ActiveCell.Offset(0, 6).Value
work2 = ActiveCell.Offset(0, 7).Value
work3 = ActiveCell.Offset(0, 8).Value

Worksheets("Report").Activate
ActiveCell.Offset(loadLine, 0).Value = work1
ActiveCell.Offset(loadLine, 1).Value = work2
ActiveCell.Offset(loadLine, 2).Value = work3
Worksheets("Data").Activate

ActiveCell.Offset(1, 0).Activate
loadLine = loadLine + 1

Wend

Can I do without the 2 Worksheet.Activate(s)???

 

[Harald Staff]

Sure. You can move data just like this without activating any of the sheets:

Worksheets("Report").Range("A2").Value =
Worksheet("Data").Range("C14").Value
or
Worksheets("Report").Cells(2, 1).Value = Worksheet("Data").Cells(14,
3).Value
or a combination of the two.

HTH. Best wishes Harald