Snasui Excel Forum

Code: Select all

Sub Jan_I_N_B()
    Dim CrntWorkBook As Workbook
    Dim SourceBook As Workbook
    Dim SourceRange As Range
    Dim Destination As Range

    Set CrntWorkBook = ActiveWorkbook
    
     With Application.FileDialog(msoFileDialogOpen)
        .Title = "File "
        .InitialFileName = ThisWorkbook.Path & "\"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xl*;*.xm*"
        .AllowMultiSelect = False
        .Show
         
        If .SelectedItems.Count > 0 Then
                Workbooks.Open .SelectedItems(1)
                Set SourceBook = ActiveWorkbook
                
    Sheets("Sale Amount").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Raw Data 3 Y].[Month].[Month]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Raw Data 3 Y].[Month].[Month]").VisibleItemsList = Array( _
        "[Raw Data 3 Y].[Month].&[12]")
    CrntWorkBook.Activate
    ActiveWindow.SmallScroll Down:=-38
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP([@Area]," & "'[" & SourceBook.Name & "]" & "Sale12 Amount'!C2:C3,2,0),0)"
    Range("D6").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=38
    Range("D6:D70").Select
    ActiveWindow.SmallScroll Down:=-38
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("D6:D70").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Sheets("1_NC").Select
    Workbooks.Open .SelectedItems(1)
    Sheets("NFC_Sale").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Raw Data 3 Y].[Month].[Month]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Raw Data 3 Y].[Month].[Month]").VisibleItemsList = Array( _
        "[Raw Data 3 Y].[Month].&[12]")
    CrntWorkBook.Activate
    ActiveWindow.SmallScroll Down:=-38
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP([@Area]," & "'[" & SourceBook.Name & "]" & "NC_Sale'!C2:C3,2,0),0)"
    Range("D6").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=38
    Range("D6:D70").Select
    ActiveWindow.SmallScroll Down:=-38
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("D6:D70").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False



    Sheets("1_BJ").Select
    Workbooks.Open .SelectedItems(1)
       Sheets("Bonjela_Sale").Select
    ActiveSheet.PivotTables("PivotTable16").PivotFields( _
        "[Raw Data 3 Y].[Month].[Month]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable16").PivotFields( _
        "[Raw Data 3 Y].[Month].[Month]").VisibleItemsList = Array( _
        "[Raw Data 3 Y].[Month].&[12]")
    CrntWorkBook.Activate
    ActiveWindow.SmallScroll Down:=-38
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP([@Area]," & "'[" & SourceBook.Name & "]" & "Bonj_Sale'!C2:C3,2,0),0)"
    Range("D6").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=38
    Range("D6:D70").Select
    ActiveWindow.SmallScroll Down:=-38
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("D6:D70").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("1_IFCN").Select
        
               SourceBook.Close False
            End If
        End With
End Sub