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.