VBA Error 1004: Application-defined or object-defined error.

[lstewart]

Hi,

I have made my first attempt at using VBA in Excel. My goal is to open an excel file and have pivot tables auto populate worksheets.

I recorded the creation of pivot tables in Excel, entered Developer and macros and copied the VB code. I have opened my new Excel file where I want the pivot tables created and paste the code into auto-start macro.

I got an Excel Error 1004: Application-defined or object-defined error.

I have listed my code below. Any help/pointers in the right direction would be lovely, I'm very new to VB.

Sub Autpen()
On Error GoTo ErrorHandler


'Makes Excel invisible while the calculations are going on to avoid flashing screens
'Rem this out when testing and debugging the code
Application.Visible = False


ThePath = ThisWorkbook.Path
Workbooks.Open Filename:=ThePath + "\ReportData.txt"


' Copy the workbook, and close the source file (having marked it as saved)
Set Wbook = ActiveWorkbook
ActiveSheet.Copy
ThisWorkbook.Sheets.Copy Before:=ActiveWorkbook.Sheets(1)
Wbook.Saved = True
Wbook.Close


'Set ReportSheet = ActiveSheet
' Mark the active workbook as saved
ActiveWorkbook.Saved = True


'Moves the sheets into a different order.
Workbooks("ExcelList.xls").Sheets(2).Move Before:=ActiveWorkbook.Sheets(1)
Workbooks("ExcelList.xls").Sheets(2).Move Before:=ActiveWorkbook.Sheets(1)
Workbooks("ExcelList.xls").Sheets(2).Move Before:=ActiveWorkbook.Sheets(1)
'Selects and copies all the data extracted from SIMS and pastes it onto the student data sheet
Sheets("ReportData").Select
Range(Sheets("ReportData").Range("A4"), Sheets("ReportData").Range("A1").SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("student Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues
'Makes all columns on student data sheet wide enough for any data used
Sheets("student Data").UsedRange.EntireRow.AutoFit
Sheets("student Data").UsedRange.EntireColumn.AutoFit
Range("A1").Select
Sheets("Pivot1").Visible = True








'**ADD CODE HERE**


'
' DayRoom Macro
' Creates a pivot table in tab Day & Room
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(-34, -12).Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"student data!R1C1:R464C9", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Day&Room!R3C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
Sheets("Day&Room").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("LG")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("LG")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("LG"), "Count of LG", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Period")
.Orientation = xlRowField
.Position = 2



'
' ByTeacher Macro
' Pivot table for By Teacher tab
'
' Keyboard Shortcut: Ctrl+z
'
ActiveCell.Select
ActiveWorkbook.Worksheets("Day&Room").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="By Teacher!R3C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion14
Sheets("By Teacher").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Teacher")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Date"), "Count of Date", xlCount


'
' Pivot2 Macro
' Pivot table for pivot2 tab
'
' Keyboard Shortcut: Ctrl+r
'
ActiveCell.Select
ActiveWorkbook.Worksheets("Day&Room").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Pivot2!R3C1", TableName:="PivotTable3" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Pivot2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Teacher")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Year")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Teacher"), "Count of Teacher", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Teacher")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Year")
.Orientation = xlPageField
.Position = 1




'
' Spikes Macro
' Pivot chart for spikes tab
'
' Keyboard Shortcut: Ctrl+s
'
ActiveWorkbook.Worksheets("Pivot2").PivotTables("PivotTable3").PivotCache. _
CreatePivotTable TableDestination:="Spikes!R3C1", TableName:="PivotTable4" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Spikes").Select
Cells(3, 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Spikes!$A$3:$C$20")
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Subject")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Period")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Subject")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Subject"), "Count of Subject", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Period")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.Shapes("Chart 1").IncrementLeft -651
ActiveSheet.Shapes("Chart 1").IncrementTop 41.25
ActiveSheet.Shapes("Chart 1").ScaleWidth 2.105645246, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").IncrementLeft 80.25
ActiveSheet.Shapes("Chart 1").IncrementTop -30.75




'
' Periods Macro
' pivot table for periods tab
'
' Keyboard Shortcut: Ctrl+f
'
ActiveCell.Select
ActiveWorkbook.Worksheets("Pivot2").PivotTables("PivotTable3").PivotCache. _
CreatePivotTable TableDestination:="Period!R3C1", TableName:="PivotTable5" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Period").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Period")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("Date"), "Count of Date", xlCount


End With
'
' HideTabs Macro
' Hides tabs in marksheet workings, analysis, pivot1 and report data.
'
' Keyboard Shortcut: Ctrl+h
'
Sheets("workings").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("analysis").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Pivot1").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("ReportData").Select
ActiveWindow.SelectedSheets.Visible = False





Sheets("Analysis").Select
Range("A1").Select




Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.Visible = True


Workbooks("ExcelList.xls").Saved = True
Workbooks("ExcelList.xls").Close




' Error-handling routine
ErrorHandler:
Application.Visible = True
MsgBox "Error " & Err.Number & " : " & Err.Description


End With
End Sub

 

[Joe4]

Welcome to the Board!

If you click the "Debug" button when you get that error, which line of VBA code does it highlight?
That is where it thinks the issue is.