Groves22
01-22-2009, 01:30 PM
Hey all...
I'm an amatuer when it comes to writing code in VBA. Whenever you have some time, can you take a look and see how to make this code better?
Right now it sounds like my computer is preparing to take flight while the macro is running, which probably isn't the best thing for my computers life.
Thanks!
Public year As String, month As String, day As String, count As String
Sub Update_Property_Profile()
Dim Response
Response = MsgBox("Did you create a copy of the profile with newest data date?", vbYesNo, "Continue?")
If Response = vbNo Then
MsgBox "Please create a copy with the new date before continuing.", vbOKOnly, "Create New Copy"
Exit Sub
End If
Call Get_Date
Call Update_Monthly_Profile
Call Update_YTD_Profile
Call Finish_Profile
End Sub
Sub Get_Date()
Dim Response
'Gather date of report
year = InputBox("Please enter year:")
month = InputBox("Please enter month (##):")
day = InputBox("Please enter day (##):")
Response = MsgBox("Is this the correct date: " & month & "/" & day & "/" & year & "?", vbYesNoCancel, "Continue?")
If Response = vbNo Then
Call Get_Date
ElseIf Respons = vbCancel Then
Exit Sub
End If
End Sub
Sub Update_Monthly_Profile()
' ************************
' * Update Monthly Sales *
' ************************
'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile _
Exhibits\Outputs\" & year & month & day & " Sales - M.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData
'Open and set up monthly sale Profile tab
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile _
Exhibits\Ohio Property Profile v2 " & month & day & year & ".xls")
Sheets(1).Visible = True
Sheets(2).Visible = True
Sheets(3).Visible = True
Sheets(4).Visible = True
Sheets(5).Visible = True
Sheets(5).Range("A4:GL65500").ClearContents
Sheets(5).Select
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("I2:M2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("S2:X2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("AA2:AG2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("AH2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("AI2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("CVA_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("K3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveCell.Replace What:=",12,", Replacement:=",13,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("K3:L3").AutoFill Destination:=Range(Cells(3, 11), Cells(count + 3, 12))
Calculate
Sheets(5).Columns("K:L").Copy
Sheets(5).Columns("K:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("WH_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("M3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(count + 3, 13))
Calculate
Sheets(5).Columns("M:M").Copy
Sheets(5).Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("MP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("T3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("T3").AutoFill Destination:=Range(Cells(3, 20), Cells(count + 3, 20))
Calculate
Sheets(5).Columns("T:T").Copy
Sheets(5).Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("LC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("W3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("W3").AutoFill Destination:=Range(Cells(3, 23), Cells(count + 3, 23))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("NP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("X3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("X3").AutoFill Destination:=Range(Cells(3, 24), Cells(count + 3, 24))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("RC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("Y3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("Y3").AutoFill Destination:=Range(Cells(3, 25), Cells(count + 3, 25))
Calculate
Sheets(5).Columns("W:Y").Copy
Sheets(5).Columns("W:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Assoc_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("U3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("U3").AutoFill Destination:=Range(Cells(3, 21), Cells(count + 3, 21))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("PD_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("V3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("V3").AutoFill Destination:=Range(Cells(3, 22), Cells(count + 3, 22))
Calculate
Sheets(5).Columns("U:V").Copy
Sheets(5).Columns("U:V").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("OT_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AG3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AG3").AutoFill Destination:=Range(Cells(3, 33), Cells(count + 3, 33))
Calculate
Sheets(5).Columns("AG:AG").Copy
Sheets(5).Columns("AG:AG").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("HM_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AI3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AI3").AutoFill Destination:=Range(Cells(3, 35), Cells(count + 3, 35))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML55_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AJ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AJ3").AutoFill Destination:=Range(Cells(3, 36), Cells(count + 3, 36))
Sheets(5).Range("AJ3").Copy
Range("AL3").Select
ActiveSheet.Paste
Range("AL3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AJ3").Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AM3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AM$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AL3:AM3").AutoFill Destination:=Range(Cells(3, 38), Cells(count + 3, 39))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML208_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AK3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AK3").AutoFill Destination:=Range(Cells(3, 37), Cells(count + 3, 37))
Sheets(5).Range("AK3").Copy
Range("AN3").Select
ActiveSheet.Paste
Range("AN3").Select
ActiveCell.Replace What:="&$AK$1", Replacement:="&$AN$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AN3").AutoFill Destination:=Range(Cells(3, 40), Cells(count + 3, 40))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML61_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AO3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AP3").Select
ActiveSheet.Paste
Range("AP3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AQ3").Select
ActiveSheet.Paste
Range("AQ3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AR3").Select
ActiveSheet.Paste
Range("AR3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AS3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AT3").Select
ActiveSheet.Paste
Range("AT3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AU3").Select
ActiveSheet.Paste
Range("AU3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AV3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AW3").Select
ActiveSheet.Paste
Range("AW3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AX3").Select
ActiveSheet.Paste
Range("AX3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AX$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3:AX3").AutoFill Destination:=Range(Cells(3, 41), Cells(count + 3, 50))
Calculate
Sheets(5).Columns("AI:AX").Copy
Sheets(5).Columns("AI:AX").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Prem_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AZ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:=",$A$3,", Replacement:=",$A3,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AZ3").AutoFill Destination:=Range(Cells(3, 52), Cells(count + 3, 52))
Calculate
Sheets(5).Columns("AZ:AZ").Copy
Sheets(5).Columns("AZ:AZ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Update formulas for counts
Sheets(5).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count + 3, 194))
Calculate
'Save and close monthly sales data workbook
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(1).Select
ActiveWorkbook.Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(year & month & day & " Sales - M.xls").Close
' *************************
' * Update Monthly Quotes *
' *************************
'Open and set up data monthly quote sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Quotes - M.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData
'Open and set up monthly quote Profile tab
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("A4:GL65500").ClearContents
Sheets(4).Select
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Quotes - M.xls").Activate
Sheets(2).Select
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("I2:M2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("S2:X2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("AA2:AG2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("AH2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("AI2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Quotes - M.xls").Activate
Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("CVA_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("K3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveCell.Replace What:=",12,", Replacement:=",13,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("K3:L3").AutoFill Destination:=Range(Cells(3, 11), Cells(count + 3, 12))
Calculate
Sheets(4).Columns("K:L").Copy
Sheets(4).Columns("K:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("WH_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("M3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(count + 3, 13))
Calculate
Sheets(4).Columns("M:M").Copy
Sheets(4).Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("MP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("T3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("T3").AutoFill Destination:=Range(Cells(3, 20), Cells(count + 3, 20))
Calculate
Sheets(4).Columns("T:T").Copy
Sheets(4).Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("LC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("W3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("W3").AutoFill Destination:=Range(Cells(3, 23), Cells(count + 3, 23))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("NP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("X3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("X3").AutoFill Destination:=Range(Cells(3, 24), Cells(count + 3, 24))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("RC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("Y3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("Y3").AutoFill Destination:=Range(Cells(3, 25), Cells(count + 3, 25))
Calculate
Sheets(4).Columns("W:Y").Copy
Sheets(4).Columns("W:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Assoc_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("U3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("U3").AutoFill Destination:=Range(Cells(3, 21), Cells(count + 3, 21))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("PD_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("V3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("V3").AutoFill Destination:=Range(Cells(3, 22), Cells(count + 3, 22))
Calculate
Sheets(4).Columns("U:V").Copy
Sheets(4).Columns("U:V").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("OT_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AG3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AG3").AutoFill Destination:=Range(Cells(3, 33), Cells(count + 3, 33))
Calculate
Sheets(4).Columns("AG:AG").Copy
Sheets(4).Columns("AG:AG").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("HM_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AI3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AI3").AutoFill Destination:=Range(Cells(3, 35), Cells(count + 3, 35))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML55_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AJ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AJ3").AutoFill Destination:=Range(Cells(3, 36), Cells(count + 3, 36))
Sheets(4).Range("AJ3").Copy
Range("AL3").Select
ActiveSheet.Paste
Range("AL3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AJ3").Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AM3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AM$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AL3:AM3").AutoFill Destination:=Range(Cells(3, 38), Cells(count + 3, 39))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML208_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AK3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AK3").AutoFill Destination:=Range(Cells(3, 37), Cells(count + 3, 37))
Sheets(4).Range("AK3").Copy
Range("AN3").Select
ActiveSheet.Paste
Range("AN3").Select
ActiveCell.Replace What:="&$AK$1", Replacement:="&$AN$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AN3").AutoFill Destination:=Range(Cells(3, 40), Cells(count + 3, 40))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML61_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AO3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AP3").Select
ActiveSheet.Paste
Range("AP3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AQ3").Select
ActiveSheet.Paste
Range("AQ3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AR3").Select
ActiveSheet.Paste
Range("AR3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AS3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AT3").Select
ActiveSheet.Paste
Range("AT3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AU3").Select
ActiveSheet.Paste
Range("AU3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AV3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AW3").Select
ActiveSheet.Paste
Range("AW3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AX3").Select
ActiveSheet.Paste
Range("AX3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AX$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3:AX3").AutoFill Destination:=Range(Cells(3, 41), Cells(count + 3, 50))
Calculate
Sheets(4).Columns("AI:AX").Copy
Sheets(4).Columns("AI:AX").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Prem_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AZ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:=",$A$3,", Replacement:=",$A3,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AZ3").AutoFill Destination:=Range(Cells(3, 52), Cells(count + 3, 52))
Calculate
Sheets(4).Columns("AZ:AZ").Copy
Sheets(4).Columns("AZ:AZ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy sales to quotes tab
Sheets(5).Select
count = Sheets(5).Range("B1").Value
Sheets(5).Range(Cells(3, 1), Cells(count + 3, 52)).Select
Selection.Copy
count = Sheets(4).Range("B1").Value
Sheets(4).Select
Sheets(4).Range(Cells(count + 4, 1), Cells(count + 4, 1)).Select
Sheets(4).Paste
'Update formulas for counts
count = Sheets(4).Range("B1").Value + Sheets(5).Range("B1").Value
Sheets(4).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count + 4, 194))
Calculate
'Save and close monthly quotes data workbook
Workbooks(year & month & day & " Quotes - M.xls").Activate
Sheets(1).Select
ActiveWorkbook.Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(year & month & day & " Quotes - M.xls").Close
End Sub
Sub Update_YTD_Profile()
' ************************
' * Update YTD Sales *
' ************************
'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Sales.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData
'Open and set up monthly sale Profile tab
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("A4:GL65500").ClearContents
Sheets(3).Select
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Sales.xls").Activate
Sheets(2).Select
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("I2:M2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("S2:X2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("AA2:AG2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("AH2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("AI2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Sales.xls").Activate
Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("CVA_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("K3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveCell.Replace What:=",12,", Replacement:=",13,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("K3:L3").AutoFill Destination:=Range(Cells(3, 11), Cells(count + 3, 12))
Calculate
Sheets(3).Columns("K:L").Copy
Sheets(3).Columns("K:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("WH_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("M3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(count + 3, 13))
Calculate
Sheets(3).Columns("M:M").Copy
Sheets(3).Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("MP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("T3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("T3").AutoFill Destination:=Range(Cells(3, 20), Cells(count + 3, 20))
Calculate
Sheets(3).Columns("T:T").Copy
Sheets(3).Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("LC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("W3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("W3").AutoFill Destination:=Range(Cells(3, 23), Cells(count + 3, 23))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("NP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("X3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("X3").AutoFill Destination:=Range(Cells(3, 24), Cells(count + 3, 24))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("RC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("Y3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("Y3").AutoFill Destination:=Range(Cells(3, 25), Cells(count + 3, 25))
Calculate
Sheets(3).Columns("W:Y").Copy
Sheets(3).Columns("W:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Assoc_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("U3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("U3").AutoFill Destination:=Range(Cells(3, 21), Cells(count + 3, 21))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("PD_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("V3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("V3").AutoFill Destination:=Range(Cells(3, 22), Cells(count + 3, 22))
Calculate
Sheets(3).Columns("U:V").Copy
Sheets(3).Columns("U:V").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("OT_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AG3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AG3").AutoFill Destination:=Range(Cells(3, 33), Cells(count + 3, 33))
Calculate
Sheets(3).Columns("AG:AG").Copy
Sheets(3).Columns("AG:AG").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("HM_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AI3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AI3").AutoFill Destination:=Range(Cells(3, 35), Cells(count + 3, 35))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML55_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AJ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AJ3").AutoFill Destination:=Range(Cells(3, 36), Cells(count + 3, 36))
Sheets(3).Range("AJ3").Copy
Range("AL3").Select
ActiveSheet.Paste
Range("AL3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AJ3").Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AM3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AM$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AL3:AM3").AutoFill Destination:=Range(Cells(3, 38), Cells(count + 3, 39))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML208_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AK3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AK3").AutoFill Destination:=Range(Cells(3, 37), Cells(count + 3, 37))
Sheets(3).Range("AK3").Copy
Range("AN3").Select
ActiveSheet.Paste
Range("AN3").Select
ActiveCell.Replace What:="&$AK$1", Replacement:="&$AN$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AN3").AutoFill Destination:=Range(Cells(3, 40), Cells(count + 3, 40))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML61_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AO3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AP3").Select
ActiveSheet.Paste
Range("AP3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AQ3").Select
ActiveSheet.Paste
Range("AQ3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AR3").Select
ActiveSheet.Paste
Range("AR3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AS3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AT3").Select
ActiveSheet.Paste
Range("AT3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AU3").Select
ActiveSheet.Paste
Range("AU3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AV3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AW3").Select
ActiveSheet.Paste
Range("AW3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AX3").Select
ActiveSheet.Paste
Range("AX3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AX$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3:AX3").AutoFill Destination:=Range(Cells(3, 41), Cells(count + 3, 50))
Calculate
Sheets(3).Columns("AI:AX").Copy
Sheets(3).Columns("AI:AX").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Prem_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AZ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:=",$A$3,", Replacement:=",$A3,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AZ3").AutoFill Destination:=Range(Cells(3, 52), Cells(count + 3, 52))
Calculate
Sheets(3).Columns("AZ:AZ").Copy
Sheets(3).Columns("AZ:AZ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Update formulas for counts
Sheets(3).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count + 3, 194))
Calculate
'Save and close monthly sales data workbook
Workbooks(year & month & day & " Sales.xls").Activate
Sheets(1).Select
ActiveWorkbook.Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(year & month & day & " Sales.xls").Close
' *************************
' * Update YTD Quotes *
' *************************
'Open and set up data monthly quote sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Quotes.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData
'Open and set up monthly quote Profile tab
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("A4:GL65500").ClearContents
Sheets(2).Select
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Quotes.xls").Activate
Sheets(2).Select
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("I2:M2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("S2:X2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("AA2:AG2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("AH2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("AI2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Quotes.xls").Activate
Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("CVA_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("K3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveCell.Replace What:=",12,", Replacement:=",13,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("K3:L3").AutoFill Destination:=Range(Cells(3, 11), Cells(count + 3, 12))
Calculate
Sheets(2).Columns("K:L").Copy
Sheets(2).Columns("K:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("WH_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("M3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(count + 3, 13))
Calculate
Sheets(2).Columns("M:M").Copy
Sheets(2).Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("MP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("T3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("T3").AutoFill Destination:=Range(Cells(3, 20), Cells(count + 3, 20))
Calculate
Sheets(2).Columns("T:T").Copy
Sheets(2).Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("LC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("W3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("W3").AutoFill Destination:=Range(Cells(3, 23), Cells(count + 3, 23))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("NP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("X3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("X3").AutoFill Destination:=Range(Cells(3, 24), Cells(count + 3, 24))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("RC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("Y3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("Y3").AutoFill Destination:=Range(Cells(3, 25), Cells(count + 3, 25))
Calculate
Sheets(2).Columns("W:Y").Copy
Sheets(2).Columns("W:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Assoc_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("U3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("U3").AutoFill Destination:=Range(Cells(3, 21), Cells(count + 3, 21))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("PD_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("V3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("V3").AutoFill Destination:=Range(Cells(3, 22), Cells(count + 3, 22))
Calculate
Sheets(2).Columns("U:V").Copy
Sheets(2).Columns("U:V").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("OT_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AG3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AG3").AutoFill Destination:=Range(Cells(3, 33), Cells(count + 3, 33))
Calculate
Sheets(2).Columns("AG:AG").Copy
Sheets(2).Columns("AG:AG").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("HM_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AI3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AI3").AutoFill Destination:=Range(Cells(3, 35), Cells(count + 3, 35))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML55_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AJ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AJ3").AutoFill Destination:=Range(Cells(3, 36), Cells(count + 3, 36))
Sheets(2).Range("AJ3").Copy
Range("AL3").Select
ActiveSheet.Paste
Range("AL3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AJ3").Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AM3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AM$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AL3:AM3").AutoFill Destination:=Range(Cells(3, 38), Cells(count + 3, 39))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML208_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AK3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AK3").AutoFill Destination:=Range(Cells(3, 37), Cells(count + 3, 37))
Sheets(2).Range("AK3").Copy
Range("AN3").Select
ActiveSheet.Paste
Range("AN3").Select
ActiveCell.Replace What:="&$AK$1", Replacement:="&$AN$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AN3").AutoFill Destination:=Range(Cells(3, 40), Cells(count + 3, 40))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML61_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AO3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AP3").Select
ActiveSheet.Paste
Range("AP3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AQ3").Select
ActiveSheet.Paste
Range("AQ3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AR3").Select
ActiveSheet.Paste
Range("AR3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AS3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AT3").Select
ActiveSheet.Paste
Range("AT3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AU3").Select
ActiveSheet.Paste
Range("AU3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AV3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AW3").Select
ActiveSheet.Paste
Range("AW3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AX3").Select
ActiveSheet.Paste
Range("AX3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AX$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3:AX3").AutoFill Destination:=Range(Cells(3, 41), Cells(count + 3, 50))
Calculate
Sheets(2).Columns("AI:AX").Copy
Sheets(2).Columns("AI:AX").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Prem_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AZ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:=",$A$3,", Replacement:=",$A3,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AZ3").AutoFill Destination:=Range(Cells(3, 52), Cells(count + 3, 52))
Calculate
Sheets(2).Columns("AZ:AZ").Copy
Sheets(2).Columns("AZ:AZ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy sales to quotes tab
Sheets(3).Select
count = Sheets(3).Range("B1").Value
Sheets(3).Range(Cells(3, 1), Cells(count + 3, 52)).Select
Selection.Copy
count = Sheets(2).Range("B1").Value
Sheets(2).Select
Sheets(2).Range(Cells(count + 4, 1), Cells(count + 4, 1)).Select
Sheets(2).Paste
'Update formulas for counts
count = Sheets(2).Range("B1").Value + Sheets(3).Range("B1").Value
Sheets(2).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count + 4, 194))
Calculate
'Save and close monthly quotes data workbook
Workbooks(year & month & day & " Quotes.xls").Activate
Sheets(1).Select
ActiveWorkbook.Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(year & month & day & " Quotes.xls").Close
End Sub
Sub Finish_Profile()
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(1).Visible = False
Sheets(2).Visible = False
Sheets(3).Visible = False
Sheets(4).Visible = False
Sheets(5).Visible = False
End Sub
I'm an amatuer when it comes to writing code in VBA. Whenever you have some time, can you take a look and see how to make this code better?
Right now it sounds like my computer is preparing to take flight while the macro is running, which probably isn't the best thing for my computers life.
Thanks!
Public year As String, month As String, day As String, count As String
Sub Update_Property_Profile()
Dim Response
Response = MsgBox("Did you create a copy of the profile with newest data date?", vbYesNo, "Continue?")
If Response = vbNo Then
MsgBox "Please create a copy with the new date before continuing.", vbOKOnly, "Create New Copy"
Exit Sub
End If
Call Get_Date
Call Update_Monthly_Profile
Call Update_YTD_Profile
Call Finish_Profile
End Sub
Sub Get_Date()
Dim Response
'Gather date of report
year = InputBox("Please enter year:")
month = InputBox("Please enter month (##):")
day = InputBox("Please enter day (##):")
Response = MsgBox("Is this the correct date: " & month & "/" & day & "/" & year & "?", vbYesNoCancel, "Continue?")
If Response = vbNo Then
Call Get_Date
ElseIf Respons = vbCancel Then
Exit Sub
End If
End Sub
Sub Update_Monthly_Profile()
' ************************
' * Update Monthly Sales *
' ************************
'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile _
Exhibits\Outputs\" & year & month & day & " Sales - M.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData
'Open and set up monthly sale Profile tab
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile _
Exhibits\Ohio Property Profile v2 " & month & day & year & ".xls")
Sheets(1).Visible = True
Sheets(2).Visible = True
Sheets(3).Visible = True
Sheets(4).Visible = True
Sheets(5).Visible = True
Sheets(5).Range("A4:GL65500").ClearContents
Sheets(5).Select
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("I2:M2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("S2:X2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("AA2:AG2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("AH2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Range("AI2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("CVA_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("K3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveCell.Replace What:=",12,", Replacement:=",13,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("K3:L3").AutoFill Destination:=Range(Cells(3, 11), Cells(count + 3, 12))
Calculate
Sheets(5).Columns("K:L").Copy
Sheets(5).Columns("K:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("WH_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("M3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(count + 3, 13))
Calculate
Sheets(5).Columns("M:M").Copy
Sheets(5).Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("MP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("T3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("T3").AutoFill Destination:=Range(Cells(3, 20), Cells(count + 3, 20))
Calculate
Sheets(5).Columns("T:T").Copy
Sheets(5).Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("LC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("W3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("W3").AutoFill Destination:=Range(Cells(3, 23), Cells(count + 3, 23))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("NP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("X3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("X3").AutoFill Destination:=Range(Cells(3, 24), Cells(count + 3, 24))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("RC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("Y3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("Y3").AutoFill Destination:=Range(Cells(3, 25), Cells(count + 3, 25))
Calculate
Sheets(5).Columns("W:Y").Copy
Sheets(5).Columns("W:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Assoc_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("U3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("U3").AutoFill Destination:=Range(Cells(3, 21), Cells(count + 3, 21))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("PD_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("V3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("V3").AutoFill Destination:=Range(Cells(3, 22), Cells(count + 3, 22))
Calculate
Sheets(5).Columns("U:V").Copy
Sheets(5).Columns("U:V").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("OT_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AG3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AG3").AutoFill Destination:=Range(Cells(3, 33), Cells(count + 3, 33))
Calculate
Sheets(5).Columns("AG:AG").Copy
Sheets(5).Columns("AG:AG").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("HM_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AI3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AI3").AutoFill Destination:=Range(Cells(3, 35), Cells(count + 3, 35))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML55_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AJ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AJ3").AutoFill Destination:=Range(Cells(3, 36), Cells(count + 3, 36))
Sheets(5).Range("AJ3").Copy
Range("AL3").Select
ActiveSheet.Paste
Range("AL3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AJ3").Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AM3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AM$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AL3:AM3").AutoFill Destination:=Range(Cells(3, 38), Cells(count + 3, 39))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML208_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AK3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AK3").AutoFill Destination:=Range(Cells(3, 37), Cells(count + 3, 37))
Sheets(5).Range("AK3").Copy
Range("AN3").Select
ActiveSheet.Paste
Range("AN3").Select
ActiveCell.Replace What:="&$AK$1", Replacement:="&$AN$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AN3").AutoFill Destination:=Range(Cells(3, 40), Cells(count + 3, 40))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML61_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AO3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AP3").Select
ActiveSheet.Paste
Range("AP3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AQ3").Select
ActiveSheet.Paste
Range("AQ3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AR3").Select
ActiveSheet.Paste
Range("AR3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AS3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AT3").Select
ActiveSheet.Paste
Range("AT3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AU3").Select
ActiveSheet.Paste
Range("AU3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AV3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AW3").Select
ActiveSheet.Paste
Range("AW3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy
Range("AX3").Select
ActiveSheet.Paste
Range("AX3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AX$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3:AX3").AutoFill Destination:=Range(Cells(3, 41), Cells(count + 3, 50))
Calculate
Sheets(5).Columns("AI:AX").Copy
Sheets(5).Columns("AI:AX").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Prem_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(5).Range("AZ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:=",$A$3,", Replacement:=",$A3,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AZ3").AutoFill Destination:=Range(Cells(3, 52), Cells(count + 3, 52))
Calculate
Sheets(5).Columns("AZ:AZ").Copy
Sheets(5).Columns("AZ:AZ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Update formulas for counts
Sheets(5).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count + 3, 194))
Calculate
'Save and close monthly sales data workbook
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(1).Select
ActiveWorkbook.Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(year & month & day & " Sales - M.xls").Close
' *************************
' * Update Monthly Quotes *
' *************************
'Open and set up data monthly quote sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Quotes - M.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData
'Open and set up monthly quote Profile tab
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("A4:GL65500").ClearContents
Sheets(4).Select
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Quotes - M.xls").Activate
Sheets(2).Select
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("I2:M2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("S2:X2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("AA2:AG2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("AH2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes - M.xls").Activate
Range("AI2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Quotes - M.xls").Activate
Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("CVA_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("K3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveCell.Replace What:=",12,", Replacement:=",13,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("K3:L3").AutoFill Destination:=Range(Cells(3, 11), Cells(count + 3, 12))
Calculate
Sheets(4).Columns("K:L").Copy
Sheets(4).Columns("K:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("WH_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("M3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(count + 3, 13))
Calculate
Sheets(4).Columns("M:M").Copy
Sheets(4).Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("MP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("T3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("T3").AutoFill Destination:=Range(Cells(3, 20), Cells(count + 3, 20))
Calculate
Sheets(4).Columns("T:T").Copy
Sheets(4).Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("LC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("W3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("W3").AutoFill Destination:=Range(Cells(3, 23), Cells(count + 3, 23))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("NP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("X3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("X3").AutoFill Destination:=Range(Cells(3, 24), Cells(count + 3, 24))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("RC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("Y3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("Y3").AutoFill Destination:=Range(Cells(3, 25), Cells(count + 3, 25))
Calculate
Sheets(4).Columns("W:Y").Copy
Sheets(4).Columns("W:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Assoc_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("U3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("U3").AutoFill Destination:=Range(Cells(3, 21), Cells(count + 3, 21))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("PD_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("V3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("V3").AutoFill Destination:=Range(Cells(3, 22), Cells(count + 3, 22))
Calculate
Sheets(4).Columns("U:V").Copy
Sheets(4).Columns("U:V").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("OT_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AG3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AG3").AutoFill Destination:=Range(Cells(3, 33), Cells(count + 3, 33))
Calculate
Sheets(4).Columns("AG:AG").Copy
Sheets(4).Columns("AG:AG").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("HM_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AI3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AI3").AutoFill Destination:=Range(Cells(3, 35), Cells(count + 3, 35))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML55_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AJ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AJ3").AutoFill Destination:=Range(Cells(3, 36), Cells(count + 3, 36))
Sheets(4).Range("AJ3").Copy
Range("AL3").Select
ActiveSheet.Paste
Range("AL3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AJ3").Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AM3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AM$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AL3:AM3").AutoFill Destination:=Range(Cells(3, 38), Cells(count + 3, 39))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML208_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AK3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AK3").AutoFill Destination:=Range(Cells(3, 37), Cells(count + 3, 37))
Sheets(4).Range("AK3").Copy
Range("AN3").Select
ActiveSheet.Paste
Range("AN3").Select
ActiveCell.Replace What:="&$AK$1", Replacement:="&$AN$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AN3").AutoFill Destination:=Range(Cells(3, 40), Cells(count + 3, 40))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML61_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AO3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AP3").Select
ActiveSheet.Paste
Range("AP3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AQ3").Select
ActiveSheet.Paste
Range("AQ3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AR3").Select
ActiveSheet.Paste
Range("AR3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AS3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AT3").Select
ActiveSheet.Paste
Range("AT3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AU3").Select
ActiveSheet.Paste
Range("AU3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AV3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AW3").Select
ActiveSheet.Paste
Range("AW3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3").Copy
Range("AX3").Select
ActiveSheet.Paste
Range("AX3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AX$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AO3:AX3").AutoFill Destination:=Range(Cells(3, 41), Cells(count + 3, 50))
Calculate
Sheets(4).Columns("AI:AX").Copy
Sheets(4).Columns("AI:AX").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Prem_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(4).Range("AZ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes - M", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:=",$A$3,", Replacement:=",$A3,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(4).Range("AZ3").AutoFill Destination:=Range(Cells(3, 52), Cells(count + 3, 52))
Calculate
Sheets(4).Columns("AZ:AZ").Copy
Sheets(4).Columns("AZ:AZ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy sales to quotes tab
Sheets(5).Select
count = Sheets(5).Range("B1").Value
Sheets(5).Range(Cells(3, 1), Cells(count + 3, 52)).Select
Selection.Copy
count = Sheets(4).Range("B1").Value
Sheets(4).Select
Sheets(4).Range(Cells(count + 4, 1), Cells(count + 4, 1)).Select
Sheets(4).Paste
'Update formulas for counts
count = Sheets(4).Range("B1").Value + Sheets(5).Range("B1").Value
Sheets(4).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count + 4, 194))
Calculate
'Save and close monthly quotes data workbook
Workbooks(year & month & day & " Quotes - M.xls").Activate
Sheets(1).Select
ActiveWorkbook.Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(year & month & day & " Quotes - M.xls").Close
End Sub
Sub Update_YTD_Profile()
' ************************
' * Update YTD Sales *
' ************************
'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Sales.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData
'Open and set up monthly sale Profile tab
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("A4:GL65500").ClearContents
Sheets(3).Select
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Sales.xls").Activate
Sheets(2).Select
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("I2:M2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("S2:X2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("AA2:AG2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("AH2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales.xls").Activate
Range("AI2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Sales.xls").Activate
Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("CVA_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("K3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveCell.Replace What:=",12,", Replacement:=",13,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("K3:L3").AutoFill Destination:=Range(Cells(3, 11), Cells(count + 3, 12))
Calculate
Sheets(3).Columns("K:L").Copy
Sheets(3).Columns("K:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("WH_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("M3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(count + 3, 13))
Calculate
Sheets(3).Columns("M:M").Copy
Sheets(3).Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("MP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("T3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("T3").AutoFill Destination:=Range(Cells(3, 20), Cells(count + 3, 20))
Calculate
Sheets(3).Columns("T:T").Copy
Sheets(3).Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("LC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("W3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("W3").AutoFill Destination:=Range(Cells(3, 23), Cells(count + 3, 23))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("NP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("X3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("X3").AutoFill Destination:=Range(Cells(3, 24), Cells(count + 3, 24))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("RC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("Y3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("Y3").AutoFill Destination:=Range(Cells(3, 25), Cells(count + 3, 25))
Calculate
Sheets(3).Columns("W:Y").Copy
Sheets(3).Columns("W:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Assoc_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("U3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("U3").AutoFill Destination:=Range(Cells(3, 21), Cells(count + 3, 21))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("PD_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("V3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("V3").AutoFill Destination:=Range(Cells(3, 22), Cells(count + 3, 22))
Calculate
Sheets(3).Columns("U:V").Copy
Sheets(3).Columns("U:V").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("OT_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AG3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AG3").AutoFill Destination:=Range(Cells(3, 33), Cells(count + 3, 33))
Calculate
Sheets(3).Columns("AG:AG").Copy
Sheets(3).Columns("AG:AG").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("HM_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AI3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AI3").AutoFill Destination:=Range(Cells(3, 35), Cells(count + 3, 35))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML55_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AJ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AJ3").AutoFill Destination:=Range(Cells(3, 36), Cells(count + 3, 36))
Sheets(3).Range("AJ3").Copy
Range("AL3").Select
ActiveSheet.Paste
Range("AL3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AJ3").Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AM3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AM$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AL3:AM3").AutoFill Destination:=Range(Cells(3, 38), Cells(count + 3, 39))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML208_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AK3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AK3").AutoFill Destination:=Range(Cells(3, 37), Cells(count + 3, 37))
Sheets(3).Range("AK3").Copy
Range("AN3").Select
ActiveSheet.Paste
Range("AN3").Select
ActiveCell.Replace What:="&$AK$1", Replacement:="&$AN$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AN3").AutoFill Destination:=Range(Cells(3, 40), Cells(count + 3, 40))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML61_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AO3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AP3").Select
ActiveSheet.Paste
Range("AP3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AQ3").Select
ActiveSheet.Paste
Range("AQ3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AR3").Select
ActiveSheet.Paste
Range("AR3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AS3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AT3").Select
ActiveSheet.Paste
Range("AT3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AU3").Select
ActiveSheet.Paste
Range("AU3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AV3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AW3").Select
ActiveSheet.Paste
Range("AW3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3").Copy
Range("AX3").Select
ActiveSheet.Paste
Range("AX3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AX$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AO3:AX3").AutoFill Destination:=Range(Cells(3, 41), Cells(count + 3, 50))
Calculate
Sheets(3).Columns("AI:AX").Copy
Sheets(3).Columns("AI:AX").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Prem_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(3).Range("AZ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Sales", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:=",$A$3,", Replacement:=",$A3,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(3).Range("AZ3").AutoFill Destination:=Range(Cells(3, 52), Cells(count + 3, 52))
Calculate
Sheets(3).Columns("AZ:AZ").Copy
Sheets(3).Columns("AZ:AZ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Update formulas for counts
Sheets(3).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count + 3, 194))
Calculate
'Save and close monthly sales data workbook
Workbooks(year & month & day & " Sales.xls").Activate
Sheets(1).Select
ActiveWorkbook.Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(year & month & day & " Sales.xls").Close
' *************************
' * Update YTD Quotes *
' *************************
'Open and set up data monthly quote sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Quotes.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData
'Open and set up monthly quote Profile tab
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("A4:GL65500").ClearContents
Sheets(2).Select
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Quotes.xls").Activate
Sheets(2).Select
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("I2:M2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("S2:X2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("AA2:AG2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("AH2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Quotes.xls").Activate
Range("AI2").Select
Sheets(2).Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Quotes.xls").Activate
Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("CVA_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("K3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
Range("L3").Select
ActiveCell.Replace What:=",12,", Replacement:=",13,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("K3:L3").AutoFill Destination:=Range(Cells(3, 11), Cells(count + 3, 12))
Calculate
Sheets(2).Columns("K:L").Copy
Sheets(2).Columns("K:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("WH_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("M3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(count + 3, 13))
Calculate
Sheets(2).Columns("M:M").Copy
Sheets(2).Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("MP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("T3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("T3").AutoFill Destination:=Range(Cells(3, 20), Cells(count + 3, 20))
Calculate
Sheets(2).Columns("T:T").Copy
Sheets(2).Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("LC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("W3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("W3").AutoFill Destination:=Range(Cells(3, 23), Cells(count + 3, 23))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("NP_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("X3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("X3").AutoFill Destination:=Range(Cells(3, 24), Cells(count + 3, 24))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("RC_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("Y3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("Y3").AutoFill Destination:=Range(Cells(3, 25), Cells(count + 3, 25))
Calculate
Sheets(2).Columns("W:Y").Copy
Sheets(2).Columns("W:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Assoc_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("U3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("U3").AutoFill Destination:=Range(Cells(3, 21), Cells(count + 3, 21))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("PD_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("V3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("V3").AutoFill Destination:=Range(Cells(3, 22), Cells(count + 3, 22))
Calculate
Sheets(2).Columns("U:V").Copy
Sheets(2).Columns("U:V").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("OT_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AG3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AG3").AutoFill Destination:=Range(Cells(3, 33), Cells(count + 3, 33))
Calculate
Sheets(2).Columns("AG:AG").Copy
Sheets(2).Columns("AG:AG").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("HM_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AI3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AI3").AutoFill Destination:=Range(Cells(3, 35), Cells(count + 3, 35))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML55_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AJ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AJ3").AutoFill Destination:=Range(Cells(3, 36), Cells(count + 3, 36))
Sheets(2).Range("AJ3").Copy
Range("AL3").Select
ActiveSheet.Paste
Range("AL3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AJ3").Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AM3").Select
ActiveCell.Replace What:="&$AJ$1", Replacement:="&$AM$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AL3:AM3").AutoFill Destination:=Range(Cells(3, 38), Cells(count + 3, 39))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML208_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AK3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AK3").AutoFill Destination:=Range(Cells(3, 37), Cells(count + 3, 37))
Sheets(2).Range("AK3").Copy
Range("AN3").Select
ActiveSheet.Paste
Range("AN3").Select
ActiveCell.Replace What:="&$AK$1", Replacement:="&$AN$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AN3").AutoFill Destination:=Range(Cells(3, 40), Cells(count + 3, 40))
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("ML61_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AO3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AP3").Select
ActiveSheet.Paste
Range("AP3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AQ3").Select
ActiveSheet.Paste
Range("AQ3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AR3").Select
ActiveSheet.Paste
Range("AR3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AS3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AT3").Select
ActiveSheet.Paste
Range("AT3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AU3").Select
ActiveSheet.Paste
Range("AU3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AV3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AW3").Select
ActiveSheet.Paste
Range("AW3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3").Copy
Range("AX3").Select
ActiveSheet.Paste
Range("AX3").Select
ActiveCell.Replace What:="&$AO$1", Replacement:="&$AX$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AO3:AX3").AutoFill Destination:=Range(Cells(3, 41), Cells(count + 3, 50))
Calculate
Sheets(2).Columns("AI:AX").Copy
Sheets(2).Columns("AI:AX").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Property Profile Macros.xls").Activate
Sheets(1).Range("Prem_Form").Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(2).Range("AZ3").Select
ActiveSheet.Paste
ActiveCell.Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sales - M", Replacement:="Quotes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:=",$A$3,", Replacement:=",$A3,", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(2).Range("AZ3").AutoFill Destination:=Range(Cells(3, 52), Cells(count + 3, 52))
Calculate
Sheets(2).Columns("AZ:AZ").Copy
Sheets(2).Columns("AZ:AZ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy sales to quotes tab
Sheets(3).Select
count = Sheets(3).Range("B1").Value
Sheets(3).Range(Cells(3, 1), Cells(count + 3, 52)).Select
Selection.Copy
count = Sheets(2).Range("B1").Value
Sheets(2).Select
Sheets(2).Range(Cells(count + 4, 1), Cells(count + 4, 1)).Select
Sheets(2).Paste
'Update formulas for counts
count = Sheets(2).Range("B1").Value + Sheets(3).Range("B1").Value
Sheets(2).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count + 4, 194))
Calculate
'Save and close monthly quotes data workbook
Workbooks(year & month & day & " Quotes.xls").Activate
Sheets(1).Select
ActiveWorkbook.Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(year & month & day & " Quotes.xls").Close
End Sub
Sub Finish_Profile()
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Sheets(1).Visible = False
Sheets(2).Visible = False
Sheets(3).Visible = False
Sheets(4).Visible = False
Sheets(5).Visible = False
End Sub