Excel macro - combine multiple sheets into pivottable

[acarrazco]

Hello, I am totaly new to VBA and I'm trying to modify a macro that was given to me but it doesn't seem to be working. I'm trying to extract data from three excel spreadsheets, put it into a combined one which creates a pivot table and sorts it into different fromats. Here is the code, can any body help?

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