I have the following code:

With Sheets("DirectorCopy")
.Paste

Why doesn't this work? Keep getting Object doesn't support property or
method. I've tried:

..Cells.Paste
..Paste
..ActiveWorksheet.Paste
..Paste("A1")
..ActiveSheet.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
(which is how the help file says to do it)

None of these work.

what will work here?

The Paste Method applies to Worksheets, Charts and some other objects, but
not the Sheets object. I think that is because a Sheets object can contain
either a Worksheet or a Chart, but since the Paste Method applies to
Worksheets and Charts individually, I'm guessing the Sheets object is too
general for the underlying code to work with. Try using...

With Worksheets("DirectoryCopy")

and see if that makes your code work.

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
I have the following code:

With Sheets("DirectorCopy")
.Paste

Why doesn't this work? Keep getting Object doesn't support property or
method. I've tried:

.Cells.Paste
.Paste
.ActiveWorksheet.Paste
.Paste("A1")
.ActiveSheet.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
(which is how the help file says to do it)

None of these work.

what will work here?

interestingly, you added a y to the sheetname. My bet was that there's a
typo in Bishop's code...

"Rick Rothstein" wrote in message
...
The Paste Method applies to Worksheets, Charts and some other objects, but
not the Sheets object. I think that is because a Sheets object can contain
either a Worksheet or a Chart, but since the Paste Method applies to
Worksheets and Charts individually, I'm guessing the Sheets object is too
general for the underlying code to work with. Try using...

With Worksheets("DirectoryCopy")

and see if that makes your code work.

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
I have the following code:

With Sheets("DirectorCopy")
.Paste

Why doesn't this work? Keep getting Object doesn't support property or
method. I've tried:

.Cells.Paste
.Paste
.ActiveWorksheet.Paste
.Paste("A1")
.ActiveSheet.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
(which is how the help file says to do it)

None of these work.

what will work here?

Changing to Worksheets did the trick. But now I can't delete the selected
rows. Here's my code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long
Dim Rng As Excel.Range

With Sheets("Tally Sheet")
.Cells.Copy
End With

With Worksheets("DirectorCopy")
'.Shapes("LazyEyeButton").Cut
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next
TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))
.Rows(ZeroRow & ":515").Delete
For i = 13 To (ZeroRow - 7) Step 8
If TypeName(Rng) = "Nothing" Then
Set Rng = .Cells(i, 1).EntireRow
Debug.Print Rng.Address
Else
Set Rng = Application.Union(Rng, .Cells(i, 1).EntireRow)
End If
Next
.Rng.Delete
.Rows("6:6").Freeze
End With
End Sub

When the last For loop finishes it still appears as though the entire
worksheet is selected... not just the rows. But I can't confirm because the
..Delete never executes.

I've tried:

..Rng.Delete
..Rows(Rng).Delete
..Selection.Delete
..Delete
(This deletes the whole worksheet)

same problem here. Object does not support property or method. What do I do?

"Rick Rothstein" wrote:

The Paste Method applies to Worksheets, Charts and some other objects, but
not the Sheets object. I think that is because a Sheets object can contain
either a Worksheet or a Chart, but since the Paste Method applies to
Worksheets and Charts individually, I'm guessing the Sheets object is too
general for the underlying code to work with. Try using...

With Worksheets("DirectoryCopy")

and see if that makes your code work.

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
I have the following code:

With Sheets("DirectorCopy")
.Paste

Why doesn't this work? Keep getting Object doesn't support property or
method. I've tried:

.Cells.Paste
.Paste
.ActiveWorksheet.Paste
.Paste("A1")
.ActiveSheet.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
(which is how the help file says to do it)

None of these work.

what will work here?

I addressed the delete issue in your previous post (subject "Looking to
select noncontiguous rows with For loop), so perhaps you could address my
response in that thread.

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
Changing to Worksheets did the trick. But now I can't delete the selected
rows. Here's my code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long
Dim Rng As Excel.Range

With Sheets("Tally Sheet")
.Cells.Copy
End With

With Worksheets("DirectorCopy")
'.Shapes("LazyEyeButton").Cut
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next
TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))
.Rows(ZeroRow & ":515").Delete
For i = 13 To (ZeroRow - 7) Step 8
If TypeName(Rng) = "Nothing" Then
Set Rng = .Cells(i, 1).EntireRow
Debug.Print Rng.Address
Else
Set Rng = Application.Union(Rng, .Cells(i, 1).EntireRow)
End If
Next
.Rng.Delete
.Rows("6:6").Freeze
End With
End Sub

When the last For loop finishes it still appears as though the entire
worksheet is selected... not just the rows. But I can't confirm because
the
.Delete never executes.

I've tried:

.Rng.Delete
.Rows(Rng).Delete
.Selection.Delete
.Delete
(This deletes the whole worksheet)

same problem here. Object does not support property or method. What do I
do?

"Rick Rothstein" wrote:

The Paste Method applies to Worksheets, Charts and some other objects,
but
not the Sheets object. I think that is because a Sheets object can
contain
either a Worksheet or a Chart, but since the Paste Method applies to
Worksheets and Charts individually, I'm guessing the Sheets object is too
general for the underlying code to work with. Try using...

With Worksheets("DirectoryCopy")

and see if that makes your code work.

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
I have the following code:

With Sheets("DirectorCopy")
.Paste

Why doesn't this work? Keep getting Object doesn't support property or
method. I've tried:

.Cells.Paste
.Paste
.ActiveWorksheet.Paste
.Paste("A1")
.ActiveSheet.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
(which is how the help file says to do it)

None of these work.

what will work here?

when deleting rows using a for/next loop, you need to start at the highest
row number

eg
for thisrow = 100 to 5 step -1
IF {condition} then
rows(thisrow).Delete
End If
next

if you loop "normally" then when you delete a row, higher numbered rows
move, and you'll miss rows. for example if you are on row 6 and you delete
it, row 7 becomes row 6 , row 8 becomes 7 and so on. Now when the NEXT row
is examonibed, row 7, you're actually on what was row 8, because row 7 moved
to replace the deleted row 6. phew. is this clear??


"Bishop" wrote in message
...
Changing to Worksheets did the trick. But now I can't delete the selected
rows. Here's my code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long
Dim Rng As Excel.Range

With Sheets("Tally Sheet")
.Cells.Copy
End With

With Worksheets("DirectorCopy")
'.Shapes("LazyEyeButton").Cut
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next
TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))
.Rows(ZeroRow & ":515").Delete
For i = 13 To (ZeroRow - 7) Step 8
If TypeName(Rng) = "Nothing" Then
Set Rng = .Cells(i, 1).EntireRow
Debug.Print Rng.Address
Else
Set Rng = Application.Union(Rng, .Cells(i, 1).EntireRow)
End If
Next
.Rng.Delete
.Rows("6:6").Freeze
End With
End Sub

When the last For loop finishes it still appears as though the entire
worksheet is selected... not just the rows. But I can't confirm because
the
.Delete never executes.

I've tried:

.Rng.Delete
.Rows(Rng).Delete
.Selection.Delete
.Delete
(This deletes the whole worksheet)

same problem here. Object does not support property or method. What do I
do?

"Rick Rothstein" wrote:

The Paste Method applies to Worksheets, Charts and some other objects,
but
not the Sheets object. I think that is because a Sheets object can
contain
either a Worksheet or a Chart, but since the Paste Method applies to
Worksheets and Charts individually, I'm guessing the Sheets object is too
general for the underlying code to work with. Try using...

With Worksheets("DirectoryCopy")

and see if that makes your code work.

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
I have the following code:

With Sheets("DirectorCopy")
.Paste

Why doesn't this work? Keep getting Object doesn't support property or
method. I've tried:

.Cells.Paste
.Paste
.ActiveWorksheet.Paste
.Paste("A1")
.ActiveSheet.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
(which is how the help file says to do it)

None of these work.

what will work here?

paste means that there's something in the clipboard that has been copied

so what did you copy? and the error message say the object doesn't exist.
My guess is that you don't have a sheet named DirectorCopy? is there a 'y'
missing?? like DirectoryCopy

Option Explicit
Sub test()
Range("A1").Copy
With Sheets("DirectorCopy")
.Paste
End With
End Sub


"Bishop" wrote in message
...
I have the following code:

With Sheets("DirectorCopy")
.Paste

Why doesn't this work? Keep getting Object doesn't support property or
method. I've tried:

.Cells.Paste
.Paste
.ActiveWorksheet.Paste
.Paste("A1")
.ActiveSheet.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
(which is how the help file says to do it)

None of these work.

what will work here?