copy paste
 

Paste is a method of the Worksheet, not a range, so you could use
something like:

Worksheets("MONDAY").Activate
Cells(CNT + 22, 1).Activate
ActiveSheet.Paste

or you could use the Destination argument of Copy. In that case,
note that Cells defaults to the ActiveSheet, so you have to qualify
it with the destination sheet.


You may find that this is somewhat more efficient:

Public Sub Montag()
Dim CNT As Long
Dim destSheet As Worksheet

Set destSheet = Worksheets("MONDAY")
With Worksheets("WEEKLY")
For CNT = 2 To 20
If .Cells(CNT, 1).Value = "x" Then _
.Range(.Cells(CNT, 6), .Cells(CNT, 9)).Copy _
Destination:=destSheet.Cells(CNT + 22, 1)
Next CNT
End With
End Sub

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.

In article ,
"Brenda" wrote:

Hello All:

I have this code:

Sub Montag()
For CNT = 2 To 20
Worksheets("WEEKLY").Activate
Dim M

Set M = Worksheets("WEEKLY").Cells(CNT, 1)
If M = "x" Then
Worksheets("WEEKLY").Range(Cells(CNT, 6), Cells(CNT,
9)).Copy
Worksheets("MONDAY").Activate
Worksheets("MONDAY").Range(Cells(CNT + 22, 1)).Paste
End If

Next CNT

End Sub

The Worksheets("MONDAY").Range(Cells(CNT+22,1)).Paste
portion is not working. I can get the range on the
Weekly sheet to copy, but not paste at all. I am
receiving an application-defined or object-defined error.

Any ideas?

Thanks,
Brenda