Here's the code I'm trying to use and keep my formulas. If I did paste the
formulas lline by line, would I paste them after the advanced filter? And how
would I reference the row if I don't know where it is in the worksheet? I
need to sign up for a class: I really like working with macros but I don't
know enough to fly alone.

Sub Break_Up_Master()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Macro for wb")
Set rng = Range("Budvars")

'extract a list of Sales Reps
ws1.Columns("A:A").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("A1").Value

For Each c In Range("J2:J" & r)
'add the rep name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Macro for wb").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
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

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

"Dave Peterson" wrote:

I think the only way to really know is for you to post that code.

To keep those formulas: One way is to loop through the visible rows (after the
data|filter|advanced filter) and copy|paste each row separately.

cinvic wrote:

Hey Mr. Peterson,
The only place where the word 'copy' appears in the code is before the
advanced filter. I tried putting in the paste special code before it and it
messed up the splitting of the master file. I got the code off a website
recommended by this discussion group and I'm trying to modify it to keep
formulas that are in the master file. So, am I really trying to copy? How do
I keep the formulas intact using an advanced filter? Light bulbs are very dim
in IL.

"Dave Peterson" wrote:

I don't see what you're copying.

Some place in your code you have:

Something.copy

Move that right before the rng.pastespecial line

cinvic wrote:

It was a fluke. I still can't get the formulas to paste after running a macro
with an advanced filter. The advanced filter's action is xlFilterCopy. I
tried putting the paste function after the Unique:=False and I still get the
time error.

I am using:
rng.PasteSpecial Format:=xlPasteFormulas "Range" is the master file that
is to be broken out into individual worksheets.

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
rng.PasteSpecial action:=xlPasteFormulas

End If

Frustrated!!!

--

Dave Peterson


--

Dave Peterson