Run time error 1004

[cinvic]

When running a great macro I got from this discussion board, my "refined"
macro creates a run time error (#1004). I added a paste special command that
sent the macro into a tizzy.

Else
Set wsNew = Sheets.Add
wsNew.Move after:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Macro for wb").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete


ws1.PasteSpecial Format:=xlPasteFormulas


End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

The macro does create the worksheets with the formulas intact. How do I
change the code to be acceptable?

Thanks for your help.

 

[Gary''s Student]

Make sure that there is still "something" to paste:

put some MsgBox (Application.CutCopyMode) statements near your paste.

 

[Dave Peterson]

Sometimes, all you have to do is rearrange your code so that you do the copy
right before you do the paste--don't put any intervening code between those two
lines.

Just a manual test....

I copied a range of cells.
(I could see the dancing ants surrounding my copied range.)

I deleted some columns.
The dancing ants disappeared.
So there was nothing to paste.

 

[cinvic]

Bingo!!! thanks again Mr. Peterson.

Sometimes, all you have to do is rearrange your code so that you do the copy
right before you do the paste--don't put any intervening code between those two
lines.

Just a manual test....

I copied a range of cells.
(I could see the dancing ants surrounding my copied range.)

I deleted some columns.
The dancing ants disappeared.
So there was nothing to paste.