macro two lists into one pivot

[andyT7]

Hi all, I need help with my macro - sorry for my english, hope you will understand.
I have lots of workbooks with different numbers of sheets - also with different names. In each sheet, there are two lists - "BEFORE" and "AFTER" status (pic 1) - also different numbers of rows. First of all, I need to put empty column to A and D with status "BEFORE" and "AFTER". Then I need tu cut and paste "AFTER" data under "BEFORE" to have everything in columns A-D (pic 2). Then I need to create pivot table from these data into this sheet (cell F1) - with layout like in pic 3. Then copy all data and paste as values.
I've tried to record macro but work just once :D - then it's not able to create new pivot table in second sheet. Also I need to put "AFTER" data under "BEFORE" data - not to cell A41. I will be very gratefull if you can somehow help me. Thank you!

Sub facing_CHG()
'
' facing_CHG Makro
'

'
    Cells.Select
    Selection.NumberFormat = "General"
    Range("A:A,D:D").Select
    Range("D1").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-1]C[1]"
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=R[-1]C"
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-1]C[1]"
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=R[-1]C"
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E2:H5").Select
    Range("H2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Cut
    ActiveWindow.SmallScroll Down:=24
    Range("A41").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-42
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "status"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "article"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "facing"
    Columns("E:H").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "F302-303!R1C1:R93C4", Version:=6).CreatePivotTable TableDestination:= _
        "F302-303!R1C6", TableName:="Kontingenčná tabuľka4", DefaultVersion:=6
    Sheets("F302-303").Select
    Cells(1, 6).Select
    With ActiveSheet.PivotTables("Kontingenčná tabuľka4")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("Kontingenčná tabuľka4").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields("status")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields("ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields("article")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Kontingenčná tabuľka4").AddDataField ActiveSheet. _
        PivotTables("Kontingenčná tabuľka4").PivotFields("facing"), "Počet z facing", _
        xlCount
    With ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields( _
        "Počet z facing")
        .Caption = "Súčet z facing"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Kontingenčná tabuľka4").RowAxisLayout xlTabularRow
    With ActiveSheet.PivotTables("Kontingenčná tabuľka4")
        .ColumnGrand = False
        .RowGrand = False
    End With
    ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields("status"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields("ID").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields("article"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields("facing"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    Range("I2").Select
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("Kontingenčná tabuľka4").PivotFields("status"). _
        PivotItems("PRED").Position = 1
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub