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? --- |
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 |