macro to autopopulate dat + copy over template worksheet with formulas

Hi All, i'm using the macro below to auto populate worksheets with incrementing date it is also copying the first sheet as a template into the other sheets. The one problem is that the templates being copied don't contain the formulas from the original sheet, and there is also one formula in particular which is pulling data from the previous sheet. This formula is:

=IF(D8="","","PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-"&TEXT(VLOOKUP(D8,Names!$A$1:$C$39,3,FALSE)+COUNTIF(E$7:E7,"PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-*")+COUNTIF(Sheet1!$E$8:$E$27,"PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-*")+COUNTIF(Sheet2!$E$8:$E$27,"PSC-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-*"),"0000"))

here you can see countif sheet1! then countif sheet2! With the marco i have created sheets for 6months, so don't want to keep copying this formula in the next worksheet and manually typing in the next sheet number i.e COUNTIF(Sheet3!$E$8:$E$27,"PSCCROY-"&VLOOKUP(D9,Names!$A$1:$C$39,2,FALSE)&"-*")

The other formulas in the worksheet are basic so don't look up previous sheets etc, but would like these in the copied over as well as the above issue.

macro:

Sub Dtpopulate()

Dim newname As String
Dim dte As Long
Dim Names As Worksheet

Set Names = ThisWorkbook.Worksheets("Names") 'variable Names now represents
'the Temp sheet.
Application.ScreenUpdating = False

Worksheets("Template").Cells().Copy 'copy whole Template sheet to clipboard

'Names.Range("endDate") is the range named "endDate" on the Temp sheet
'likewise for startDate

For dte = Names.Range("endDate") To Names.Range("startDate") Step -1
'dte is dateSerial (daynumber counting from 1-1-1900)

newname = WorksheetFunction.Text(dte, "dd-mmm-yy")
Sheets.Add.Name = newname 'Sheets.add returns a worksheet object
'of which property name is set
'it is inserted before the active sheet.

With Sheets(newname).Cells 'paste from clipboard to all cells in new sheet

.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With

Next dte
Application.ScreenUpdating = True

End Sub



Thanks in advance for your help