Expand|Select|Wrap|Line Numbers
- Sub Update_Land_Actuals()
- On Error Resume Next
- If InputBox("Enter password to continue", "Centex Homes") <> "***" Then Exit Sub
- Sheets("Detail").Visible = True
- Application.DisplayAlerts = False
- Sheets("Actuals").Delete
- Application.DisplayAlerts = True
- Sheets("Detail").Select
- Sheets("Detail").Cells.ClearContents
- Call GetRetrievedData
- Call CombineData
- Call FormatTotalsSheet
- Application.ScreenUpdating = True
- MsgBox "Land Actuals updated", vbOKOnly + vbInformation, "Centex Homes"
- End Sub
- Private Sub GetRetrievedData()
- On Error Resume Next
- Dim i As Integer, CompositeBook As String
- CompositeBook = ThisWorkbook.Name
- Const DownloadFile As String = "ActualsDownload"
- Application.ScreenUpdating = False
- Application.ShowWindowsInTaskbar = False
- For i = 1 To 3
- Workbooks.Open Filename:=DownloadPath & DownloadFile & i & ".xls"
- ActiveSheet.Copy Before:=Workbooks(CompositeBook).Sheets(1)
- ActiveSheet.Move After:=Sheets(Sheets.Count)
- Windows(DownloadFile & i & ".xls").Close (False)
- Next i
- Application.ShowWindowsInTaskbar = True
- Sheets("Detail").Select
- End Sub
- Private Sub CombineData()
- Dim i As Integer
- Const DownloadFile As String = "ActualsDownload"
- Application.ScreenUpdating = False
- Sheets("Detail").Select
- Cells.Clear
- For i = 1 To 3
- Sheets(DownloadFile & i).Select
- Range(Range("A2"), Range("A2").End(xlToRight)).Select
- Range(Selection, Selection.End(xlDown)).Select
- Selection.Copy
- Sheets("Detail").Select
- Selection.End(xlDown).Select
- Range("A65000").End(xlUp).Offset(1, 0).Select
- ActiveSheet.Paste
- Application.CutCopyMode = False
- Next i
- Application.DisplayAlerts = False
- For i = 1 To 3
- Sheets(DownloadFile & i).Select
- ActiveWindow.SelectedSheets.Delete
- Next
- Cells.EntireColumn.AutoFit
- Application.DisplayAlerts = True
- End Sub
- Private Sub FormatTotalsSheet()
- On Error Resume Next
- Dim cell As Range
- Application.ScreenUpdating = False
- Cells.Font.Size = 8
- For Each cell In Range(Range("A2"), Range("A2").End(xlDown))
- cell = cell & "LD" & cell.Offset(0, 1)
- Next
- For Each cell In Range(Range("G2:H2"), Range("G2:H2").End(xlDown))
- cell = cell.Value
- Next
- Columns("B:B").Delete Shift:=xlToLeft
- Range("A1") = "JOB"
- Range("B1") = "CC"
- Range("C1") = "DESCRIPTION"
- Range("D1") = "VENDOR"
- Range("E1") = "REFERENCE"
- Range("F1") = "AMOUNT"
- Range("G1") = "DATE"
- Columns("F:F").Style = "Comma"
- Rows("1:1").Font.Bold = True
- Rows("1:1").HorizontalAlignment = xlCenter
- Cells.EntireColumn.AutoFit
- Columns("G:G").NumberFormat = "mm/dd/yy"
- Range("A1").CurrentRegion.Select
- Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
- , Order2:=xlAscending, Key3:=Range("G2"), Order3:=xlAscending, Header:= _
- xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
- Range("A2").Select
- ActiveWindow.FreezePanes = True
- End Sub
- Sub Create_Actuals_Pivot()
- '****** DYNAMIC PIVOT TABLE********
- On Error Resume Next
- If InputBox("Enter password to continue", "Centex Homes") <> "***" Then Exit Sub
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Sheets("Detail").Visible = True
- Sheets("Detail").Select
- Sheets("Actuals").Delete
- Application.DisplayAlerts = True
- '****** DYNAMIC PIVOT TABLE START********
- Sheets("Detail").Select
- ActiveSheet.Range("A1").Select
- Dim DataSource As Range, i As Integer, cell As Range
- Set DataSource = Range("A1").CurrentRegion
- Application.ScreenUpdating = False
- ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
- DataSource, TableDestination:="", TableName:="CCPivotTable"
- ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
- ActiveSheet.Cells(3, 1).Select
- ActiveSheet.PivotTables("CCPivotTable").SmallGrid = False
- ActiveSheet.PivotTables("CCPivotTable").AddFields RowFields:="CC", PageFields _
- :="JOB"
- With ActiveSheet.PivotTables("CCPivotTable").PivotFields("AMOUNT")
- .Orientation = xlDataField
- .Caption = "Cost Code Totals"
- .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
- End With
- Application.CommandBars("PivotTable").Visible = False
- '****** DYNAMIC PIVOT TABLE END********
- ActiveSheet.Name = "Actuals"
- For Each cell In Range(Range("B5"), Range("B5").End(xlDown))
- cell.Offset(0, 1).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],CostCodes!R2C1:R40000C3,2,FALSE)),"""", VLOOKUP(RC[-2],CostCodes!R2C1:R40000C3,2,FALSE))"
- Next
- Cells.Font.Size = 8
- Cells.EntireColumn.AutoFit
- Range("C4") = "Description"
- Rows("4:4").Font.Bold = True
- Rows("4:4").HorizontalAlignment = xlCenter
- Range("B1").Select
- Range("B1").Font.Size = 11
- Range("B1").Font.Bold = 11
- Range("C1").FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],LandJobs!R[1]C[-2]:R[300]C[1],4,FALSE)),"""", VLOOKUP(RC[-1],LandJobs!R[1]C[-2]:R[300]C[1],4,FALSE))"
- Range("C1").InsertIndent 1
- Range("C1").Font.Size = 11
- Range("C1").Font.Bold = True
- Range("C1:E1").Merge True
- ActiveSheet.ScrollArea = "A1:B200"
- ActiveSheet.PivotTables("CCPivotTable").EnableDrilldown = False
- Sheets("Detail").Visible = False
- Call ShowDetailButton
- Application.ScreenUpdating = True
- End Sub
- Private Sub Detail()
- On Error GoTo Err_DetailHandler
- Dim CostCode As String
- CostCode = Range("A" & ActiveCell.Row).Value
- Application.ScreenUpdating = False
- If WorksheetFunction.Sum(ActiveCell.EntireRow) = 0 Then Exit Sub
- If Range("A" & ActiveCell.Row).Value > 70000 And Range("A" & ActiveCell.Row).Value < 79999 = False Then Exit Sub
- ActiveSheet.PivotTables("CCPivotTable").EnableDrilldown = True
- ActiveCell.ShowDetail = True
- ActiveSheet.Name = "Detail for C.C. " & CostCode
- Cells.Font.Size = 8
- Columns("F:F").Style = "Comma"
- Range("A1").Select
- Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
- OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
- Range("A2").Select
- ActiveWindow.FreezePanes = True
- Columns("G:G").NumberFormat = "mm/dd/yy"
- Range("F10000").End(xlUp).Offset(1, 0).Select
- ActiveCell = "=SUM(" & ActiveCell.Offset(-1, 0).End(xlUp).Address & ":" & ActiveCell.Offset(-1, 0).Address & ")"
- ActiveCell.Font.Bold = True
- Cells.EntireColumn.AutoFit
- ActiveSheet.Buttons.Add(535, 16.5, 126.75, 18).Select
- Selection.OnAction = "HideDetail"
- Selection.Characters.Text = "Hide Detail"
- With Selection.Characters(Start:=1, Length:=11).Font
- .FontStyle = "Bold"
- .Size = 8
- End With
- Range("A2").Select
- Exit Sub
- Err_DetailHandler:
- If Err.Number = 1004 Then
- Range("B" & ActiveCell.Row).Select
- Resume
- Else
- Exit Sub
- End If
- End Sub
- Private Sub HideDetail()
- Application.DisplayAlerts = False
- If Left(ActiveSheet.Name, 10) = "Detail for" Then ActiveWindow.SelectedSheets.Delete
- Sheets("Actuals").Select
- ActiveSheet.PivotTables("CCPivotTable").EnableDrilldown = False
- Application.DisplayAlerts = True
- End Sub
- Private Sub ShowDetailButton()
- Rows("2:2").RowHeight = 26.25
- ActiveSheet.Buttons.Add(141.75, 20.25, 129, 16.5).Select
- Selection.OnAction = "Detail"
- Selection.Characters.Text = "Show Cost Code Detail"
- With Selection.Characters(Start:=1, Length:=21).Font
- .FontStyle = "Bold"
- .Size = 8
- End With
- Range("a5").Select
- ActiveWindow.FreezePanes = True
- End Sub