rshanka2
05-10-2016, 05:25 AM
Hello, Request for help
I am trying to move a pivot table from one excel sheet to other excel workbook. It gives the following error
I also tried pivottable 1 instead of 5. Also gives user defined error. Any help is appreciated. Thanks a lot everyone
Sub Rollup()
'
' Rollup Macro
'
'
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Sheets("Enterprise Lane Roll Up").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh- ERROR MESSAGE16147
Columns("A:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("J2:J" & LR), Type:=xlFillDefault
Range("J2:J" & LR).Select
Columns("J:J").Select
Range("J397").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Lane ID"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("J2:J" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:K" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],R[-1]C,R[-1]C+1)"
Selection.AutoFill Destination:=Range("K3:K" & LR), Type:=xlFillDefault
Range("K3:K" & LR).Select
Columns("K:K").Select
Range("K397").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "O"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("L2:L" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:L" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]=R[-1]C[-1],RC[-7]=R[-1]C[-7]),"""",RC[-7])"
Selection.AutoFill Destination:=Range("L2:L" & LR), Type:=xlFillDefault
Range("L2:L" & LR).Select
ActiveWindow.SmallScroll Down:=-66
Columns("L:L").Select
Range("L331").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "O St Merge"
Range("M1").Select
ActiveWindow.SmallScroll Down:=-15
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("L2:L" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:M" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("L2:L" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:M" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=R[-1]C[-2],CONCATENATE(R[-1]C,"","",RC[-1]),RC[-1])"
Selection.AutoFill Destination:=Range("M2:M" & LR), Type:=xlFillDefault
Range("M2:M" & LR).Select
Columns("M:M").Select
Range("M396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "O St Roll Up"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Len"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Columns("N:N").Select
Range("N396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("N2:N" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:N" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("O1").Select
ActiveCell.FormulaR1C1 = "O St Roll Up"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],RC[-2],"""")"
Selection.AutoFill Destination:=Range("O2:O" & LR), Type:=xlFillDefault
Range("O2:O" & LR).Select
Range("O405").Select
Columns("O:O").Select
Selection.delete Shift:=xlToLeft
Range("O1").Select
ActiveCell.FormulaR1C1 = "O St Roll Up"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],"""",RC[-2])"
Selection.AutoFill Destination:=Range("O2:O" & LR), Type:=xlFillDefault
Range("O2:O" & LR).Select
Columns("O:O").Select
Selection.delete Shift:=xlToLeft
Range("O1").Select
ActiveCell.FormulaR1C1 = "O St Roll Up"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C,RC[-2])"
Selection.AutoFill Destination:=Range("O2:O" & LR), Type:=xlFillDefault
Range("O2:O" & LR).Select
Columns("O:O").Select
Range("O396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("L:N").Select
Range("N1").Activate
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Range("M1").Select
ActiveCell.FormulaR1C1 = "D"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("H2:H" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:O" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-2]=R[-1]C[-2],RC[-5]=R[-1]C[-5]),"""",RC[-5])"
Selection.AutoFill Destination:=Range("M2:M" & LR), Type:=xlFillDefault
Range("M2:M" & LR).Select
Columns("M:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "D Merge"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=R[-1]C[-3],CONCATENATE(R[-1]C,"","",RC[-1]),RC[-1])"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Columns("H:H").ColumnWidth = 30.71
Columns("E:E").ColumnWidth = 27.14
Columns("L:L").ColumnWidth = 22.14
Columns("H:H").ColumnWidth = 17.14
Columns("E:E").ColumnWidth = 13.86
Columns("N:N").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N6").Select
Range("O1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Len"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O" & LR), Type:=xlFillDefault
Range("O2:O" & LR).Select
Columns("O:O").Select
Range("O396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("O409").Select
Selection.End(xlUp).Select
Range("P1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "D Roll Up"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("O2:O" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:P" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=R[-1]C[-5],R[-1]C,RC[-2])"
Selection.AutoFill Destination:=Range("P2:P" & LR), Type:=xlFillDefault
Range("P2:P" & LR).Select
ActiveWindow.SmallScroll Down:=-72
Columns("P:P").Select
Range("P324").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("M:O").Select
Range("O1").Activate
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Columns("J:J").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("J1:M1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("L:M").Select
Selection.Replace What:=",,*", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="(blank)", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("N1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "O ST"
Range("N1").Select
ActiveCell.FormulaR1C1 = "O ST EL"
Range("O1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "D St EL"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-2],1)="","",LEFT(RC[-2],LEN(RC[-2])-1),RC[-2])"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Columns("N:N").Select
Range("N396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-471
Columns("L:L").Select
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Range("N2").Select
ActiveWindow.SmallScroll Down:=-3
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-2])="","",LEFT(RC[-2],LEN(RC[-2])-1),RC[-2])"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
ActiveWindow.SmallScroll Down:=-33
Columns("N:N").Select
Range("N363").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("L:L").Select
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Range("N1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Remove?"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:P" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=R[-1]C[-3],99,1)"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Selection.End(xlUp).Select
Range("N2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("N2:N" & LR).Select
Selection.ClearContents
Columns("N:N").Select
Selection.delete Shift:=xlToLeft
Range("N1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Remove?"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=R[-1]C[-3],""Remove"",""Keen"")"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Columns("N:N").Select
Range("N396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N394").Select
Selection.End(xlUp).Select
Range("O1").Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Sort"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Remove"",99,1)"
Selection.AutoFill Destination:=Range("O2:O20000"), Type:=xlFillDefault
Range("O2:O20000").Select
Columns("O:O").Select
Range("O20000").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("O2:O20000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:P20000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("P12").Select
Range("P15000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.delete
Columns("O:O").Select
Range("O14982").Activate
Selection.delete Shift:=xlToLeft
Range("L1").Select
ActiveWindow.LargeScroll Down:=1
Range("L37").Select
ActiveWindow.LargeScroll Down:=1
Range("L73").Select
ActiveWindow.LargeScroll Down:=1
Range("L109").Select
ActiveWindow.LargeScroll Down:=1
Range("L145").Select
ActiveWindow.LargeScroll Down:=-1
Range("L109").Select
ActiveWindow.LargeScroll Down:=-1
Range("L73").Select
ActiveWindow.LargeScroll Down:=-1
Range("L37").Select
ActiveWindow.LargeScroll Down:=-1
Range("L1").Select
ActiveWindow.SmallScroll Down:=-21
Columns("N:N").Select
Selection.delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Cut
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.delete Shift:=xlToLeft
Columns("J:K").Select
Range("K1").Activate
Selection.delete Shift:=xlToLeft
Range("H9:I9").Select
ActiveWindow.SmallScroll Down:=-9
Columns("A:I").Select
Selection.Replace What:="(blank)", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("C:I").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:I").Select
Selection.Replace What:="(blank)", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("A2:A20000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:R20000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Formula Copy").Select
Range("J1:R1").Select
Selection.Copy
Sheets("Enterprise Lane Roll Up").Select
Range("J2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("J2:R" & LR), Type:=xlFillDefault
Range("J2:R" & LR).Select
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 1
Columns("J:R").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "O Type"
Range("O1").Select
ActiveCell.FormulaR1C1 = "D Type"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Lane Name"
Columns("J:K").Select
Selection.delete Shift:=xlToLeft
Columns("K:L").Select
Selection.delete Shift:=xlToLeft
Columns("L:M").Select
Selection.delete Shift:=xlToLeft
Columns("J:L").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("J1:L1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("K8").Select
Range("M1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Keep"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""0 to 0"",99,1)"
Selection.AutoFill Destination:=Range("M2:M20000"), Type:=xlFillDefault
Range("M2:M20000").Select
I am trying to move a pivot table from one excel sheet to other excel workbook. It gives the following error
I also tried pivottable 1 instead of 5. Also gives user defined error. Any help is appreciated. Thanks a lot everyone
Sub Rollup()
'
' Rollup Macro
'
'
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Sheets("Enterprise Lane Roll Up").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh- ERROR MESSAGE16147
Columns("A:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("J2:J" & LR), Type:=xlFillDefault
Range("J2:J" & LR).Select
Columns("J:J").Select
Range("J397").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Lane ID"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("J2:J" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:K" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],R[-1]C,R[-1]C+1)"
Selection.AutoFill Destination:=Range("K3:K" & LR), Type:=xlFillDefault
Range("K3:K" & LR).Select
Columns("K:K").Select
Range("K397").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "O"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("L2:L" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:L" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]=R[-1]C[-1],RC[-7]=R[-1]C[-7]),"""",RC[-7])"
Selection.AutoFill Destination:=Range("L2:L" & LR), Type:=xlFillDefault
Range("L2:L" & LR).Select
ActiveWindow.SmallScroll Down:=-66
Columns("L:L").Select
Range("L331").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "O St Merge"
Range("M1").Select
ActiveWindow.SmallScroll Down:=-15
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("L2:L" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:M" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("L2:L" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:M" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=R[-1]C[-2],CONCATENATE(R[-1]C,"","",RC[-1]),RC[-1])"
Selection.AutoFill Destination:=Range("M2:M" & LR), Type:=xlFillDefault
Range("M2:M" & LR).Select
Columns("M:M").Select
Range("M396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "O St Roll Up"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Len"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Columns("N:N").Select
Range("N396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("N2:N" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:N" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("O1").Select
ActiveCell.FormulaR1C1 = "O St Roll Up"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],RC[-2],"""")"
Selection.AutoFill Destination:=Range("O2:O" & LR), Type:=xlFillDefault
Range("O2:O" & LR).Select
Range("O405").Select
Columns("O:O").Select
Selection.delete Shift:=xlToLeft
Range("O1").Select
ActiveCell.FormulaR1C1 = "O St Roll Up"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],"""",RC[-2])"
Selection.AutoFill Destination:=Range("O2:O" & LR), Type:=xlFillDefault
Range("O2:O" & LR).Select
Columns("O:O").Select
Selection.delete Shift:=xlToLeft
Range("O1").Select
ActiveCell.FormulaR1C1 = "O St Roll Up"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C,RC[-2])"
Selection.AutoFill Destination:=Range("O2:O" & LR), Type:=xlFillDefault
Range("O2:O" & LR).Select
Columns("O:O").Select
Range("O396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("L:N").Select
Range("N1").Activate
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Range("M1").Select
ActiveCell.FormulaR1C1 = "D"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("H2:H" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:O" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-2]=R[-1]C[-2],RC[-5]=R[-1]C[-5]),"""",RC[-5])"
Selection.AutoFill Destination:=Range("M2:M" & LR), Type:=xlFillDefault
Range("M2:M" & LR).Select
Columns("M:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "D Merge"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=R[-1]C[-3],CONCATENATE(R[-1]C,"","",RC[-1]),RC[-1])"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Columns("H:H").ColumnWidth = 30.71
Columns("E:E").ColumnWidth = 27.14
Columns("L:L").ColumnWidth = 22.14
Columns("H:H").ColumnWidth = 17.14
Columns("E:E").ColumnWidth = 13.86
Columns("N:N").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N6").Select
Range("O1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Len"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O" & LR), Type:=xlFillDefault
Range("O2:O" & LR).Select
Columns("O:O").Select
Range("O396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("O409").Select
Selection.End(xlUp).Select
Range("P1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "D Roll Up"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("O2:O" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:P" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=R[-1]C[-5],R[-1]C,RC[-2])"
Selection.AutoFill Destination:=Range("P2:P" & LR), Type:=xlFillDefault
Range("P2:P" & LR).Select
ActiveWindow.SmallScroll Down:=-72
Columns("P:P").Select
Range("P324").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("M:O").Select
Range("O1").Activate
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Columns("J:J").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("J1:M1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("L:M").Select
Selection.Replace What:=",,*", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="(blank)", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("N1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "O ST"
Range("N1").Select
ActiveCell.FormulaR1C1 = "O ST EL"
Range("O1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "D St EL"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-2],1)="","",LEFT(RC[-2],LEN(RC[-2])-1),RC[-2])"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Columns("N:N").Select
Range("N396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-471
Columns("L:L").Select
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Range("N2").Select
ActiveWindow.SmallScroll Down:=-3
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-2])="","",LEFT(RC[-2],LEN(RC[-2])-1),RC[-2])"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
ActiveWindow.SmallScroll Down:=-33
Columns("N:N").Select
Range("N363").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("L:L").Select
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Range("N1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Remove?"
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("K2:K" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:P" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=R[-1]C[-3],99,1)"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Selection.End(xlUp).Select
Range("N2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("N2:N" & LR).Select
Selection.ClearContents
Columns("N:N").Select
Selection.delete Shift:=xlToLeft
Range("N1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Remove?"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=R[-1]C[-3],""Remove"",""Keen"")"
Selection.AutoFill Destination:=Range("N2:N" & LR), Type:=xlFillDefault
Range("N2:N" & LR).Select
Columns("N:N").Select
Range("N396").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N394").Select
Selection.End(xlUp).Select
Range("O1").Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Sort"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Remove"",99,1)"
Selection.AutoFill Destination:=Range("O2:O20000"), Type:=xlFillDefault
Range("O2:O20000").Select
Columns("O:O").Select
Range("O20000").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("O2:O20000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:P20000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("P12").Select
Range("P15000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.delete
Columns("O:O").Select
Range("O14982").Activate
Selection.delete Shift:=xlToLeft
Range("L1").Select
ActiveWindow.LargeScroll Down:=1
Range("L37").Select
ActiveWindow.LargeScroll Down:=1
Range("L73").Select
ActiveWindow.LargeScroll Down:=1
Range("L109").Select
ActiveWindow.LargeScroll Down:=1
Range("L145").Select
ActiveWindow.LargeScroll Down:=-1
Range("L109").Select
ActiveWindow.LargeScroll Down:=-1
Range("L73").Select
ActiveWindow.LargeScroll Down:=-1
Range("L37").Select
ActiveWindow.LargeScroll Down:=-1
Range("L1").Select
ActiveWindow.SmallScroll Down:=-21
Columns("N:N").Select
Selection.delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Cut
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.delete Shift:=xlToLeft
Columns("J:K").Select
Range("K1").Activate
Selection.delete Shift:=xlToLeft
Range("H9:I9").Select
ActiveWindow.SmallScroll Down:=-9
Columns("A:I").Select
Selection.Replace What:="(blank)", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("C:I").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:I").Select
Selection.Replace What:="(blank)", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Select
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort.SortFields.add Key _
:=Range("A2:A20000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Enterprise Lane Roll Up").Sort
.SetRange Range("A1:R20000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Formula Copy").Select
Range("J1:R1").Select
Selection.Copy
Sheets("Enterprise Lane Roll Up").Select
Range("J2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("J2:R" & LR), Type:=xlFillDefault
Range("J2:R" & LR).Select
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 1
Columns("J:R").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "O Type"
Range("O1").Select
ActiveCell.FormulaR1C1 = "D Type"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Lane Name"
Columns("J:K").Select
Selection.delete Shift:=xlToLeft
Columns("K:L").Select
Selection.delete Shift:=xlToLeft
Columns("L:M").Select
Selection.delete Shift:=xlToLeft
Columns("J:L").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("J1:L1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("K8").Select
Range("M1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Keep"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""0 to 0"",99,1)"
Selection.AutoFill Destination:=Range("M2:M20000"), Type:=xlFillDefault
Range("M2:M20000").Select