Can someone please help me debug the code in red? I was following Bill Jelen's Youtube tutorial episode 1211 on how to fix the hard coded parts of the macro -- the fact that pivot table went to new work sheet and that new sheet is called ".." -- I need to fix this hard coded part, as well as need Data Sheet as a variable.
Please help! I use the record macro function, and only need these two variables fixed. Thank you!
COPY / PASTED directly from <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>:
Sub CreatePivot()
'
' CreatePivot Macro
'
' Keyboard Shortcut: Ctrl+r
'
DataSheet = ActiveSheet.Name
Sheets.Add
NewSheet = ActiveSheet.Name
Range("A4").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:Z4"), Type:=xlFillDefault
Range("A4:Z4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:3").Select
Range("A3").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Cells.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
DataSheet & "!R1C1:R1048576C26", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=NewSheet & "!R3C1", TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion14
Sheets(NewSheet).Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Order/ship reference")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Item")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Received")
.Orientation = xlRowField
.Position = 3
End With
Please help! I use the record macro function, and only need these two variables fixed. Thank you!
COPY / PASTED directly from <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>:
Sub CreatePivot()
'
' CreatePivot Macro
'
' Keyboard Shortcut: Ctrl+r
'
DataSheet = ActiveSheet.Name
Sheets.Add
NewSheet = ActiveSheet.Name
Range("A4").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:Z4"), Type:=xlFillDefault
Range("A4:Z4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:3").Select
Range("A3").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Cells.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
DataSheet & "!R1C1:R1048576C26", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=NewSheet & "!R3C1", TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion14
Sheets(NewSheet).Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Order/ship reference")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Item")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Received")
.Orientation = xlRowField
.Position = 3
End With