software

When running a great macro I got from this discussion board, my quot;refinedquot;
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(quot;Macro for wbquot;).Range(quot;L1:L2quot;), _
CopyToRange:=wsNew.Range(quot;A1quot;), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns(quot;J:Lquot;).Deletews1.PasteSpecial Format:=xlPasteFormulasEnd Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) gt; 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 quot;somethingquot; to paste:

put some MsgBox (Application.CutCopyMode) statements near your paste.
--
Gary''s Studentquot;cinvicquot; wrote:

gt; When running a great macro I got from this discussion board, my quot;refinedquot;
gt; macro creates a run time error (#1004). I added a paste special command that
gt; sent the macro into a tizzy.
gt;
gt; Else
gt; Set wsNew = Sheets.Add
gt; wsNew.Move after:=Worksheets(Worksheets.Count)
gt; wsNew.Name = c.Value
gt; rng.AdvancedFilter Action:=xlFilterCopy, _
gt; CriteriaRange:=Sheets(quot;Macro for wbquot;).Range(quot;L1:L2quot;), _
gt; CopyToRange:=wsNew.Range(quot;A1quot;), _
gt; Unique:=False
gt; End If
gt; Next
gt; ws1.Select
gt; ws1.Columns(quot;J:Lquot;).Delete
gt;
gt;
gt; ws1.PasteSpecial Format:=xlPasteFormulas
gt;
gt;
gt; End Sub
gt; Function WksExists(wksName As String) As Boolean
gt; On Error Resume Next
gt; WksExists = CBool(Len(Worksheets(wksName).Name) gt; 0)
gt; End Function
gt;
gt; The macro does create the worksheets with the formulas intact. How do I
gt; change the code to be acceptable?
gt;
gt; 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:
gt;
gt; When running a great macro I got from this discussion board, my quot;refinedquot;
gt; macro creates a run time error (#1004). I added a paste special command that
gt; sent the macro into a tizzy.
gt;
gt; Else
gt; Set wsNew = Sheets.Add
gt; wsNew.Move after:=Worksheets(Worksheets.Count)
gt; wsNew.Name = c.Value
gt; rng.AdvancedFilter Action:=xlFilterCopy, _
gt; CriteriaRange:=Sheets(quot;Macro for wbquot;).Range(quot;L1:L2quot;), _
gt; CopyToRange:=wsNew.Range(quot;A1quot;), _
gt; Unique:=False
gt; End If
gt; Next
gt; ws1.Select
gt; ws1.Columns(quot;J:Lquot;).Delete
gt;
gt; ws1.PasteSpecial Format:=xlPasteFormulas
gt;
gt; End Sub
gt; Function WksExists(wksName As String) As Boolean
gt; On Error Resume Next
gt; WksExists = CBool(Len(Worksheets(wksName).Name) gt; 0)
gt; End Function
gt;
gt; The macro does create the worksheets with the formulas intact. How do I
gt; change the code to be acceptable?
gt;
gt; Thanks for your help.

--

Dave Peterson

Bingo!!! thanks again Mr. Peterson.

quot;Dave Petersonquot; wrote:

gt; Sometimes, all you have to do is rearrange your code so that you do the copy
gt; right before you do the paste--don't put any intervening code between those two
gt; lines.
gt;
gt; Just a manual test....
gt;
gt; I copied a range of cells.
gt; (I could see the dancing ants surrounding my copied range.)
gt;
gt; I deleted some columns.
gt; The dancing ants disappeared.
gt; So there was nothing to paste.
gt;
gt; cinvic wrote:
gt; gt;
gt; gt; When running a great macro I got from this discussion board, my quot;refinedquot;
gt; gt; macro creates a run time error (#1004). I added a paste special command that
gt; gt; sent the macro into a tizzy.
gt; gt;
gt; gt; Else
gt; gt; Set wsNew = Sheets.Add
gt; gt; wsNew.Move after:=Worksheets(Worksheets.Count)
gt; gt; wsNew.Name = c.Value
gt; gt; rng.AdvancedFilter Action:=xlFilterCopy, _
gt; gt; CriteriaRange:=Sheets(quot;Macro for wbquot;).Range(quot;L1:L2quot;), _
gt; gt; CopyToRange:=wsNew.Range(quot;A1quot;), _
gt; gt; Unique:=False
gt; gt; End If
gt; gt; Next
gt; gt; ws1.Select
gt; gt; ws1.Columns(quot;J:Lquot;).Delete
gt; gt;
gt; gt; ws1.PasteSpecial Format:=xlPasteFormulas
gt; gt;
gt; gt; End Sub
gt; gt; Function WksExists(wksName As String) As Boolean
gt; gt; On Error Resume Next
gt; gt; WksExists = CBool(Len(Worksheets(wksName).Name) gt; 0)
gt; gt; End Function
gt; gt;
gt; gt; The macro does create the worksheets with the formulas intact. How do I
gt; gt; change the code to be acceptable?
gt; gt;
gt; gt; Thanks for your help.
gt;
gt; --
gt;
gt; Dave Peterson
gt;