Formula SheetName Syntax

[municipiodeloiza]

<--
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 procedure:
-->

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]'!D2101>=Cover!D11)*('[SheetName]!D2101<=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").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?
--->

 

[Bob Phillips]

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((D2101>=Cover!D11)*(D2101<=Cover!D12)*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").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").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)

<--
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 procedure:
-->

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]'!D2101>=Cover!D11)*('[SheetName]!D2101<=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").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?
--->

 

[LoizaMIS]

Thanks. I've simplified the code, but the formula is being added, not
to the new sheet, instead to an existing sheet that is like a table of
contents of all sheets. I forgot to add in the previous post where's
going the formula. Here's the

ws.Cells(iRow, 7).Formula =
SUMPRODUCT(('[SheetName]'!D2101>=Cover!D11)*('[SheetName]!D2101<=Cover­!D12)*'[SheetName]'!E2:E101)


ws = The table of contents sheet
SheetName = The new created sheet <-- This is where I'm confused...

 

[Bob Phillips]

Well, you know what the sheet is going to be called even before you create
it, so use that

sFormula = "=SUMPRODUCT(('[" & Me.SegSocNo & "]'!D2101>=Cover!D11)*" &
_
"('[" & Me.SegSocNo & "]!D2101<=Cover­!D12)*" & _
"'[[" & Me.SegSocNo & "]!E2:E101)"
ws.Cells(iRow, 7).Formula = sFormula



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Thanks. I've simplified the code, but the formula is being added, not
to the new sheet, instead to an existing sheet that is like a table of
contents of all sheets. I forgot to add in the previous post where's
going the formula. Here's the

ws.Cells(iRow, 7).Formula =
SUMPRODUCT(('[SheetName]'!D2101>=Cover!D11)*('[SheetName]!D2101<=Cover­!
D12)*'[SheetName]'!E2:E101)


ws = The table of contents sheet
SheetName = The new created sheet <-- This is where I'm confused...