How to make this VBA better

[Groves22]

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!

[vba]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").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").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").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").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
[/vba]

[Bob Phillips]

You have to be kidding mate!

Do you really expect us to sort that out for you.

Tips:
- get rid of the selects
- turn off screenupdating
- turn off automatic calculation
- put repeating code into modules

Then post back when you have broken the back.

[Simon Lloyd]

xld, i'm with you not far short of 2000 lines of code posted!!!

To Groves22, you would be better off starting again and understand that you do not need to select or activate a sheet/cell/range to manipulate it, make use of FOR NEXT loops to make sheets visible/hidden, if you have to write code to get rid of #REF then you need to seriously look at the structure of your worksheet and how you are manipulating it.....the list goes on!

[Groves22]

You have to be kidding mate!

Do you really expect us to sort that out for you.

No I never really expected someone to sit down and completely makeover my VBA. Just tips and suggestions, like a snip-it of code that could be better, then I build from that.

and understand that you do not need to select or activate a sheet/cell/range to manipulate it, make use of FOR NEXT loops to make sheets visible/hidden

That is something I am unsure how to do, expect the little help I got last time. Could you post an example for me? Thanks!!

if you have to write code to get rid of #REF then you need to seriously look at the structure of your worksheet and how you are manipulating it

I have 10 or so formulas that get pasted from the macro workbook. They are dummy formulas wioth #REF added in. When the formula is pasted to the profile workbook, the #REF gets replaced with the correct sheet. It was just an easy way for me to get the formulas to paste without error.

If you know a better way to do that.... by all mean help me out!!

I know I posted a lot, but like I said, I am a total amatuar when it comes to VBA. I just want to learn how to make it better, any advice is great, even if it's only a few lines of code I could build on!

[CreganTur]

You definately need to make use of loops. A few good loop structures would get rid of a lot of the similar code statements you use over and over and over again.

If you need more information on loops, click on Articles under my name and read the loops article.

[Groves22]

OK... So I had an Eureka moment!!

I took this code:

[vba]'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[/vba]

And I turned it into this code:

[vba]'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
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Range("F2:H2", Sheets(2).Range("F2:H2").End(xlDown)).Copy _
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("B3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("I2:M2", Sheets(2).Range("I2:M2").End(xlDown)).Copy _
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("F3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("S2:X2", Sheets(2).Range("S2:X2").End(xlDown)).Copy _
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("N3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("AA2:AG2", Sheets(2).Range("AA2:AG2").End(xlDown)).Copy _
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("Z3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("AH2", Sheets(2).Range("AH2").End(xlDown)).Copy _
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AH3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("AI2", Sheets(2).Range("AI2").End(xlDown)).Copy _
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AY3")
[/vba]

That's a good start, right?!?!

[Bob Phillips]

Yeahg, that is exactly the sort of thing I meant when I said get rid of the selects. It usually condenes 6 lines or so down to 1.

[nst1107]

Good start, yes. Here's an application of the loops everybody is suggesting:

[VBA]Sheets(1).Visible = True
Sheets(2).Visible = True
Sheets(3).Visible = True
Sheets(4).Visible = True
Sheets(5).Visible = True [/VBA]

Change to:
[VBA]Dim i As Integer
For i = 1 to 5
Sheets(i).Visible = True
Next[/VBA]

[Bob Phillips]

You are joking!

Who would add a loop, which is making code less clean and adds a processing overhead, to save 2 lines!

[nst1107]

I would? (Actually, I would....) Maybe not the best application, but it's not a bad example of how to use a loop.

[Paul_Hossler]

Another technique to consider is to Dim some objects and to use the With/End With to save typing.

I've been told that it's also slightly more effecient since there are fewer "de-references" to go up the '.dot' change

I like it be they make (IMHO) the code easier to follow


[vba]
Dim wsSrc As Worksheet, wsDest As Worksheet

Set wsSrc = Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(2)
Set wsDest = Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(5)

With wsSrc
.Range("I2:M2", .Range("I2:M2").End(xlDown)).Copy (wsDest.Range("F3"))
.Range("S2:X2", .Range("I2:M2").End(xlDown)).Copy (wsDest.Range("N3"))

'etc.

End With
[/vba]

Paul

[mdmackillop]

Where you are using identical lines of code with different variables, pass the values to a separate routine to carry out the repeating code. Instead of
[VBA]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))
[/VBA]

Create a separate routine such as
[VBA]Sub DoStuff(wb1 As Workbook, rng As String, wb2 As Workbook, ReplVal1 As String, ReplVal2, FillRng As Long)
wb1.Activate
Sheets(1).Range(rng).Copy wb2.Sheets(5).Range("V3")
wb2.Range("V3").Replace What:=ReplVal1, Replacement:=Year & Month & Day
wb2.Range("V3").Replace What:="#REF", Replacement:=ReplVal1
wb2.Sheets(5).Range("V3").AutoFill Destination:=Range(Cells(3, FillRng), Cells(Count + 3, FillRng))
End Sub[/VBA]

and pass the values to it

[VBA]DoStuff _
Workbooks("Property Profile Macros.xls"), _
"PD_Form", _
Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls"), _
"20081231", _
"Sheet2", _
22
[/VBA]

By keeping the values on separate lines, it is easier to see the variables when maintenance is required.

Note. The code as shown will probably error, but the principle is sound.

[Groves22]

Thanks md... I will give that a shot a bit later...

As an update, I turned 489 lines into 262 thus far. Thanks for the tips!

Keep them coming, I'm eager to learn!!!

[mdmackillop]

Post your revised code so we can see where you are.

[Groves22]

Post your revised code so we can see where you are.

Here is the part I improved. This is just 1/4 or the overall code, but the other parts are similar.

Also, is there a way to declare a workbook as a name? I tried, but was unsuccessful

i.e. [vba]Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate[/vba] could be somethinng like [vba]P_Profile.Activate[/vba]

The main code:

[vba]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 & "?", vbYesNo, "Continue?")
If Response = vbNo Then
Call Get_Date
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")
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("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")
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
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("F2:H2", Sheets(2).Range("F2:H2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("B3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("I2:M2", Sheets(2).Range("I2:M2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("F3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("S2:X2", Sheets(2).Range("S2:X2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("N3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("AA2:AG2", Sheets(2).Range("AA2:AG2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("Z3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("AH2", Sheets(2).Range("AH2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AH3")
Workbooks(year & month & day & " Sales - M.xls").Sheets(2).Range("AI2", Sheets(2).Range("AI2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AY3")

'Copy down formulas in profile tab
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Activate
Sheets(5).Range("B1").FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Sheets(5).Range("B1").Value
Sheets(5).Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Sheets(5).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").Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Sheets(1).Range("CVA_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("K3")
Range("K3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("K3").Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("K3").Copy Range("L3")
Range("L3").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").Sheets(1).Range("WH_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("M3")
Range("M3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("M3").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").Sheets(1).Range("MP_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("T3")
Range("T3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("T3").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").Sheets(1).Range("LC_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("W3")
Range("W3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("W3").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").Sheets(1).Range("NP_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("X3")
Range("X3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("X3").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").Sheets(1).Range("RC_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("Y3")
Range("Y3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("Y3").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").Sheets(1).Range("Assoc_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("U3")
Range("U3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("U3").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").Sheets(1).Range("PD_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("V3")
Range("V3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("V3").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").Sheets(1).Range("OT_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AG3")
Range("AG3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AG3").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").Sheets(1).Range("HM_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AI3")
Range("AI3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AI3").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").Sheets(1).Range("ML55_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AJ3")
Range("AJ3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AJ3").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 Sheets(5).Range("AL3")
Range("AL3").Replace What:="&$AJ$1", Replacement:="&$AL$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AJ3").Copy Sheets(5).Range("AM3")
Range("AM3").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").Sheets(1).Range("ML208_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AK3")
Range("AK3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AK3").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 Sheets(5).Range("AN3")
Range("AN3").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").Sheets(1).Range("ML61_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AO3")
Range("AO3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AO3").Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AP3")
Range("AP3").Replace What:="&$AO$1", Replacement:="&$AP$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AQ3")
Range("AQ3").Replace What:="&$AO$1", Replacement:="&$AQ$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AR3")
Range("AR3").Replace What:="&$AO$1", Replacement:="&$AR$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AS3")
Range("AS3").Replace What:="&$AO$1", Replacement:="&$AS$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AT3")
Range("AT3").Replace What:="&$AO$1", Replacement:="&$AT$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AU3")
Range("AU3").Replace What:="&$AO$1", Replacement:="&$AU$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AV3")
Range("AV3").Replace What:="&$AO$1", Replacement:="&$AV$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AW3")
Range("AW3").Replace What:="&$AO$1", Replacement:="&$AW$1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AX3")
Range("AX3").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").Sheets(1).Range("Prem_Form").Copy Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Sheets(5).Range("AZ3")
Range("AZ3").Replace What:="20081231", Replacement:=year & month & day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AZ3").Replace What:="#REF", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AZ3").Replace What:="Sheet2", Replacement:="Sheet1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AZ3").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").Application.DisplayAlerts = False
Workbooks(year & month & day & " Sales - M.xls").Sheets(1).Delete
Workbooks(year & month & day & " Sales - M.xls").Application.DisplayAlerts = True
Workbooks(year & month & day & " Sales - M.xls").Save
Workbooks(year & month & day & " Sales - M.xls").Close[/vba]

[Bob Phillips]

Also, is there a way to declare a workbook as a name? I tried, but was unsuccessful

i.e. [vba]Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate[/vba] could be somethinng like [vba]P_Profile.Activate[/vba]

[vba]

Workbooks("Ohio Property Profile v2 " & Format(Date, "mmddyy") & ".xls").Activate
[/vba]

or

[vba]

Set P_Profile = Workbooks("Ohio Property Profile v2 " & Format(Date, "mmddyy") & ".xls")
P_Profile.Activate[/vba]

[mdmackillop]

[VBA]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")
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)

'@@@@@@@@@@@ Use With statement
Sheets(2).Range("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")
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
'Filter and paste policy data from data sheet to profile tab

'@@@@@@@@@@@@@@@@@ Set Variables for workbooks
'@@@@@@@@@@@@@@@@@ Combine Year & Month & Day into one new variable and use that (It is not a good idea to use these names as Variable as they are Functions in VBA)


Workbooks(Year & Month & Day & " Sales - M.xls").Activate
Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(2).Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(2).Range("F2:H2", Sheets(2).Range("F2:H2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Range("B3")
Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(2).Range("I2:M2", Sheets(2).Range("I2:M2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Range("F3")
Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(2).Range("S2:X2", Sheets(2).Range("S2:X2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Range("N3")
Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(2).Range("AA2:AG2", Sheets(2).Range("AA2:AG2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Range("Z3")
Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(2).Range("AH2", Sheets(2).Range("AH2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Range("AH3")
Workbooks(Year & Month & Day & " Sales - M.xls").Sheets(2).Range("AI2", Sheets(2).Range("AI2").End(xlDown)).Copy Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Range("AY3")

'Copy down formulas in profile tab
Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Activate
Sheets(5).Range("B1").FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
Count = Sheets(5).Range("B1").Value
Sheets(5).Range("A3").AutoFill Destination:=Range(Cells(3, 1), Cells(Count + 3, 1))
Sheets(5).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").Sheets(2).ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Property Profile Macros.xls").Sheets(1).Range("CVA_Form").Copy Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Range("K3")


'@@@@@@@@@@@@@@@ Trim our redundant terms from Find statements

Range("K3").Replace What:="20081231", Replacement:=Year & Month & Day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("K3").Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("K3").Copy Range("L3")
Range("L3").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

'@@@@@@@@@@@@@@ Deleted to save space

'@@@@@@@@@@@@@@@@@@@@@@ Split different code areas into separate routines and call them in turn. Easier to find problems and maintain


Workbooks("Property Profile Macros.xls").Sheets(1).Range("ML61_Form").Copy Workbooks("Ohio Property Profile v2 " & Month & Day & Year & ".xls").Sheets(5).Range("AO3")
Range("AO3").Replace What:="20081231", Replacement:=Year & Month & Day, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AO3").Replace What:="#REF", Replacement:="Sheet2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(5).Range("AO3").Copy Sheets(5).Range("AP3")
'@@@@@@@@@@@@@@ Deleted to save space
[/VBA]

[Groves22]

Why do I get this error:

" Run-Time error '1004'
Application-defined or object-defined error. "

when I try to execute this code:

[VBA]P_Profile.Sheets(5).Range(Cells(3, 1), Cells(count1 + 3, 52)).Copy P_Profile.Sheets(4).Range(Cells(count2 + 4, 1), Cells(count2 + 4, 1))[/VBA]

count1 has value of 173
count2 has value of 467

[mdmackillop]

[VBA]P_Profile.Sheets(5).Range(Cells(3, 1), Cells(count1 + 3, 52)).Copy P_Profile.Sheets(4).Cells(count2 + 4, 1) [/VBA]
Paste to the top left cell of the target range to avoid possible range size problems.