VBA to Import data from another Workbook

[SamarthSalunkhe]

Hi All,

I am using the below code to import data from another sheet, but it is work's in a given range only.

But I want to import data from the available pivot table of sheets, can someone help me with this.

Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A2:L50").Copy
        ThisWorkbook.Worksheets("Import Data").Range("A2").PasteSpecial xlPasteValues
        OpenBook.Close False
        
    End If
    Application.ScreenUpdating = True
End Sub

Thanks in Advance

 

[Saurabhj]

Hi, As per below line, code copy Range A2 to L50.

OpenBook.Sheets(1).Range("A2:L50").Copy

Assuming Pivot Table name is PivotTable4 and the pivot table is available in sheet "Pivot". Below code will copy the data from Pivot table and paste in A1 cell of Sheet1.

        Sheets("Pivot").Activate
        ActiveSheet.PivotTables("PivotTable4").PivotSelect "", xlDataAndLabel, True
        Selection.Copy
        Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False

 

[SamarthSalunkhe]

Hi, As per below line, code copy Range A2 to L50.

OpenBook.Sheets(1).Range("A2:L50").Copy

Assuming Pivot Table name is PivotTable4 and the pivot table is available in sheet "Pivot". Below code will copy the data from Pivot table and paste in A1 cell of Sheet1.

        Sheets("Pivot").Activate
        ActiveSheet.PivotTables("PivotTable4").PivotSelect "", xlDataAndLabel, True
        Selection.Copy
        Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False

Hi Saurabh,

I have tried my best, but facing an issue with the code.

Could you please provide code doing changes?

Thank you in advance.