Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula SheetName Syntax
<--
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? --- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula SheetName Syntax
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? --- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula SheetName Syntax
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]'!D2:D101=Cover!D11)*('[SheetName]!D2:D101<=Cover*!D12)*'[SheetName]'!E2:E101) ws = The table of contents sheet SheetName = The new created sheet <-- This is where I'm confused... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula SheetName Syntax
Well, you know what the sheet is going to be called even before you create
it, so use that sFormula = "=SUMPRODUCT(('[" & Me.SegSocNo & "]'!D2:D101=Cover!D11)*" & _ "('[" & Me.SegSocNo & "]!D2:D101<=Cover*!D12)*" & _ "'[[" & Me.SegSocNo & "]!E2:E101)" ws.Cells(iRow, 7).Formula = sFormula -- HTH Bob Phillips (remove nothere from email address if mailing direct) "LoizaMIS" wrote in message oups.com... 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]'!D2:D101=Cover!D11)*('[SheetName]!D2:D101<=Cover*! D12)*'[SheetName]'!E2:E101) ws = The table of contents sheet SheetName = The new created sheet <-- This is where I'm confused... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically update SheetName in workbook sub if SheetName changes | Excel Discussion (Misc queries) | |||
Reference Sheetname in Formula | Excel Discussion (Misc queries) | |||
Sheetname formula | Excel Worksheet Functions | |||
Formula that returns the sheetname | Excel Discussion (Misc queries) | |||
How do I have a formula lookup a sheetname? | Excel Worksheet Functions |