Copy cells in another worksheet without activating

I have a macro that copies cells from another worksheet in a loop. The
macro switches between sheets many times while the macro is runnin
because I am activating one sheet, then copying to another. How can I
copy cells in another worksheet without first activating it? Here is
the loop that I am now using:

If Not c Is Nothing Then
firstAddress = c.Address
Do

Sheets("Files").Activate
range(c.Address).Select
Selection.Resize(1, 6).Select
Selection.Offset(0, -2).Select
Selection.Copy
Set rng = Sheets("Directory").Cells(Rows.count,
2).End(xlUp)(2).Offset(0, -1)
rng.PasteSpecial xlValues
Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress
End If

I tried using Sheets("Files").range(c.Address).Select but I get a
"select method of range class failed" error. Anyone got any ideas?
Thanks.

You can not select on a sheet that is not activated. give this a try...

If Not c Is Nothing Then
firstAddress = c.Address
Do

Sheets("Files").range(c.Address).Resize(1, 6)..Offset(0,
-2).Copy
Set rng = Sheets("Directory").Cells(Rows.count,
2).End(xlUp)(2).Offset(0, -1)
rng.PasteSpecial xlValues
Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress
End If

--
HTH...

Jim Thomlinson


"Jon" wrote:

> I have a macro that copies cells from another worksheet in a loop. The
> macro switches between sheets many times while the macro is runnin
> because I am activating one sheet, then copying to another. How can I
> copy cells in another worksheet without first activating it? Here is
> the loop that I am now using:
>
> If Not c Is Nothing Then
> firstAddress = c.Address
> Do
>
> Sheets("Files").Activate
> range(c.Address).Select
> Selection.Resize(1, 6).Select
> Selection.Offset(0, -2).Select
> Selection.Copy
> Set rng = Sheets("Directory").Cells(Rows.count,
> 2).End(xlUp)(2).Offset(0, -1)
> rng.PasteSpecial xlValues
> Set c = .FindNext(c)
>
> Loop While Not c Is Nothing And c.Address <> firstAddress
> End If
>
> I tried using Sheets("Files").range(c.Address).Select but I get a
> "select method of range class failed" error. Anyone got any ideas?
> Thanks.
>
>

That worked beautifully. Did exactly what I wanted. Thanks for your
help Jim.