If the formula is being added to the sheet just create and that ran ge
refres to a range in that sheet, you don't need the sheet name, just

=SUMPRODUCT((D2:D101=Cover!D11)*(D2:D101<=Cover!D 12)*E2:E101)

Your code can be simplified too

Sheets("Formato").Select // This is the new sheet
Range("A1:F1").Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = Me.SegSocNo
ActiveSheet.Move After:=Worksheets(Worksheets.Count)
Columns("A:A").Select
Selection.ColumnWidth = 15.29
Columns("B:B").Select
Selection.ColumnWidth = 46.86
Columns("C:C").Select
Selection.ColumnWidth = 15.57
Columns("D:D").Select
Selection.ColumnWidth = 15.29
Columns("E:E").Select
Selection.ColumnWidth = 14.86
Columns("F:F").Select
Selection.ColumnWidth = 16.86
Application.GoTo Sheets("Portada").Range("A1"), True


becomes

Sheets("Formato").Select // This is the new sheet
Range("A1:F1").Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = Me.SegSocNo
ActiveSheet.Move After:=Worksheets(Worksheets.Count)
Columns("A:A").ColumnWidth = 15.29
Columns("B:B").ColumnWidth = 46.86
Columns("C:C").ColumnWidth = 15.57
Columns("D:D").ColumnWidth = 15.29
Columns("E:E").ColumnWidth = 14.86
Columns("F:F").ColumnWidth = 16.86
Application.GoTo Sheets("Portada").Range("A1"), True



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
<--
Ok. Here's the deal.

I have a procedure that when I press a button creates a worksheet, copy
the format from a template sheet and changes the sheet name to a user
assigned client number entered on a field in a form. It also add a line
to a general list of client on another worksheet. The problem resides
on this sheet. I need to copy a formula but I'm not sure on the correct
syntax. This is the procedu
--

Private Sub btnNewSupplier_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SupplierList") // This is the general list

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

ws.Cells(iRow, 1).Value = Me.SupplierName.Value
ws.Cells(iRow, 2).Value = Me.SegSocNo.Value
ws.Cells(iRow, 3).Value = Me.Address.Value
ws.Cells(iRow, 4).Value = Me.City.Value
ws.Cells(iRow, 5).Value = Me.State.Value
ws.Cells(iRow, 6).Value = Me.ZipCode.Value
ws.Cells(iRow, 9).Value = Me.cbo480.Value

<--
This is the formula I want to copy:


=SUMPRODUCT(('[SheetName]'!D2:D101=Cover!D11)*('[SheetName]!D2:D101<=Cover!
D12)*'[SheetName]'!E2:E101)

The formula brings a total between two dates in two cells in a sheet
named "Cover".
--


Sheets("Formato").Select // This is the new sheet
Range("A1:F1").Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = Me.SegSocNo
ActiveSheet.Move After:=Worksheets(Worksheets.Count)
Columns("A:A").Select
Selection.ColumnWidth = 15.29
Columns("B:B").Select
Selection.ColumnWidth = 46.86
Columns("C:C").Select
Selection.ColumnWidth = 15.57
Columns("D:D").Select
Selection.ColumnWidth = 15.29
Columns("E:E").Select
Selection.ColumnWidth = 14.86
Columns("F:F").Select
Selection.ColumnWidth = 16.86
Application.GoTo Sheets("Portada").Range("A1"), True

SupplierName.Value = ""
Address.Value = ""
City.Value = ""
State.Value = "PR"
ZipCode.Value = ""
cbo480.Value = ""
SegSocNo.Value = ""


End Sub

<--
Now my question is: Since the sheet name isn't assigned until the
copying procedure ends, what's the correct syntax to refer to the newly
created sheet?
---