Carrying formulas into other sheets

[dbondmelo]

Hi Everyone,
Looking for some help here. I have this formula that looks at a column and creates a new sheet based on the persons name in column D.. Works great but once that is not working is in my master SHEET (before it's copied) I have a formula and that formula does not carry over into the other sheets.

Any ideas?

thanks
Dereck

    Dim rng As Range
     
    With Sheets("Page1_1")
        .AutoFilterMode = False
        Sheets.Add().Name = "Temp"
        .Range("D2", .Range("D2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
         
        For Each rng In Sheets("Temp").UsedRange.Offset(1).Resize(Sheets("Temp").UsedRange.Rows.Count - 1)
            .Range("D2").CurrentRegion.AutoFilter field:=4, Criteria1:=rng
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = rng
            .AutoFilter.Range.Copy Sheets(rng.Text).Range("A1")
        Next rng
         
        .AutoFilterMode = False
        Application.DisplayAlerts = False
        Sheets("Temp").Delete
        Application.DisplayAlerts = True
         
    End With

[dbondmelo]

Just ran this in STEP into mode and noticed something. If I get to the part of the code below. The first sheet that's created has the formula still. But the second, third and 4th loose the formula.

I'm assuming it's part of the RANGE.COPY SHEETS(RNG.TEXT) part.. but I have no idea what to change it to.

For Each rng In Sheets("Temp").UsedRange.Offset(1).Resize(Sheets("Temp").UsedRange.Rows.Count - 1)
            .Range("D2").CurrentRegion.AutoFilter field:=4, Criteria1:=rng
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = rng
            .AutoFilter.Range.Copy Sheets(rng.Text).Range("A1")
        Next rng