I tried again, but still having trouble. My apology...let me try again.
I declared FPsh as Worksheet in my declarations, but no luck. I
originally tried declaring as a String.
I need to assign a variable for the active worksheet Name property,
since I run the code in different workbooks and the worksheet name
changes each time I run the code. I want to assign a variable to the
worksheet Name property and use that variable in my SUMPRODUCT formula.
Below is the troublesome area of code from my module. My problem seems
to be incorrect syntax for variable(s) and how to write the SUMPRODUCT
formula code so that the variable is used for worksheet name.
Your help is sincerely appreciated... Mike
-------------------------------------------------------------------------------
Sub IS_Setup2005()
Dim fName As String
Dim msg As String
Dim FPsh As Worksheet
Dim FPsheetName2 As Worksheet
Application.DisplayAlerts = True
Sheet1.Select
Set FPsheetName2 = ActiveWorksheet
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("N1").Select
'Range(Worksheets("Sheet2").Select
Range("AZ1:AZ100").Value =
Worksheets("Sheet2").Range("D1:E100").Value
Range("N2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$AZ$2:$AZ88"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("O2").Formula =
"=IF(N1<>"""",VLOOKUP(N1,'2005'!B$6:C$99,2,FALSE),"""")"
Range("N2:O2").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Selection.AutoFill Destination:=Range("N2:O1000"),
Type:=xlFillDefault
Range("N:N").ColumnWidth = 34.29
Range("O:O").ColumnWidth = 4.43
Range("A1").Select
Set FPsh = Worksheets.ActiveSheet.Name
'ActiveSheet.Name = FPsh
Worksheets("2005").Select
Range("E6").Formula = "=SUMPRODUCT((" & FPsh & "!$E$1:$E$1000=$C6)*" &
"(" & FPsh & "!$B$1:$B$1000>(--(""2004/12/31"")))*" & "((" & FPsh &
"!$B$1:$B$1000<(--(G$4))))*" & "(((" & FPsh & "!$J$1:$J$1000)" & ")"
Range("G6:G96").Formula = Range("E6:E96").Formula
Range("I6:I96").Formula = Range("E6:E96").Formula
Range("K6:K96").Formula = Range("E6:E96").Formula
Range("M6:M96").Formula = Range("E6:E96").Formula
Range("O6:O96").Formula = Range("E6:E96").Formula
Range("Q6:Q96").Formula = Range("E6:E96").Formula
Range("S6:S96").Formula = Range("E6:E96").Formula
Range("U6:U96").Formula = Range("E6:E96").Formula
Range("W6:W96").Formula = Range("E6:E96").Formula
Range("Y6:Y96").Formula = Range("E6:E96").Formula
Range("AA6:AA96").Formula = Range("E6:E96").Formula
Application.DisplayAlerts = True
ActiveWindow.LargeScroll up:=3
Range("B2").Select
MsgBox "Verify Office Name is completed"
fName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xls),
*.xls")
If fName <> "False" Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
msg = "File is saved at location " & _
(ActiveWorkbook.FileFormat = xlNormal) & _
vbNewLine & "full path: " & ActiveWorkbook.FullName
MsgBox msg
Application.DisplayAlerts = True
Else
MsgBox ("You did not save the file")
End If
End Sub
------------------------------------------------------------------------------------------------------------------------
