.Paste

[Bishop]

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?

 

[Rick Rothstein]

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.

 

[Patrick Molloy]

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

 

[Patrick Molloy]

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

 

[Bishop]

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?

 

[Patrick Molloy]

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??

 

[Rick Rothstein]

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.