Hi - im new to this forum and im not great with code but i've been trying to figure this error out for days now and im about to lose it! Recorded a macro to create a pivot table and paste as values into a new workbook. Keeps giving me a runtime error 424 Object required message. Changed color to RED where it stops. Please help!!! Thanks in advance!
Sub MRG_RTV_FINAL()
'
' MRG_RTV_FINAL Macro
'
'
ActiveCell.FormulaR1C1 = "UPC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "UNITS"
Range("A2").Select
Sheets("SKUs").Select
Sheets("SKUs").Name = "SHEET1"
Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SHEET1!R1C1:R1291C2").CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet2").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("UPC"), "Sum of UPC", xlSum
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("UNITS"), "Sum of UNITS", xlSum
With ActiveSheet.PivotTables("PivotTable2").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of UPC")
.Orientation = xlRowField
.Position = 1
End With
Cells.Select
Selection.Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:3").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "UPC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "UNITS"
Range("A2").Select
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select
ChDir "Q:\MRG RTVs"
ActiveWorkbook.SaveAs Filename:="Q:\MRG RTVs\CURRENT_FINAL.xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub MRG_RTV_FINAL()
'
' MRG_RTV_FINAL Macro
'
'
ActiveCell.FormulaR1C1 = "UPC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "UNITS"
Range("A2").Select
Sheets("SKUs").Select
Sheets("SKUs").Name = "SHEET1"
Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SHEET1!R1C1:R1291C2").CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet2").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("UPC"), "Sum of UPC", xlSum
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("UNITS"), "Sum of UNITS", xlSum
With ActiveSheet.PivotTables("PivotTable2").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of UPC")
.Orientation = xlRowField
.Position = 1
End With
Cells.Select
Selection.Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:3").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "UPC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "UNITS"
Range("A2").Select
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select
ChDir "Q:\MRG RTVs"
ActiveWorkbook.SaveAs Filename:="Q:\MRG RTVs\CURRENT_FINAL.xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub