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.

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

put some MsgBox (Application.CutCopyMode) statements near your paste.
--
Gary''s Student


"cinvic" wrote:

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.

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 wrote:

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.

--

Dave Peterson

Bingo!!! thanks again Mr. Peterson.

"Dave Peterson" wrote:

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 wrote:

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.

--

Dave Peterson