Macro to pull data from another workbook

[logesh excel]

Hi,

I'm trying to figure out a macro which will help me to pull data from another workbook and past it in A10 (in attached WB). For example, in combo box if i select Master WB 1 then the macro should copy the information from sheet 1 in Master WB 1 and paste it in current WB (attached WB) in A10. Similarly if i select Master WB 2 from the combo box then the macro should copy the information from sheet 1 in Master WB 2 and paste it in current WB in A10 (not beneath as i don't want to see the data of Master WB 1 when i select Master WB 2 or other).

[AlphaFrog]

Assign this macro to the dropdown control.

Change the folder path within the code of the master workbook files to suit.

The macro assumes the value in the drop down is the filename & .xlsx e.g.; Master workbook1.xlsx

Sub Copy_Master_WB_Data()
    
    Dim strPath As String
    Dim strFile As String
    Dim DD As DropDown
    
    strPath = "C:\Test\"    'Master workbook folder
    Set DD = Sheets("Sheet1").DropDowns(Application.Caller)
    
    If DD.ListIndex > -1 Then
        strFile = strPath & DD.List(DD.ListIndex) & ".xlsx"
        If Len(Dir$(strFile)) Then
            Application.ScreenUpdating = False
            If Range("A10") <> "" Then Range("A10", Cells.Find("*", , , , 1, 2)).EntireRow.ClearContents
            With Workbooks.Open(strFile)
                .Sheets(1).UsedRange.Copy
                ThisWorkbook.Sheets("Sheet1").Range("A10").PasteSpecial xlPasteValuesAndNumberFormats
                .Close SaveChanges:=False
            End With
            Range("A10").Select
            Application.ScreenUpdating = True
        Else
            MsgBox strFile, vbExclamation, "File Not Found"
        End If
    End If
    
End Sub

[logesh excel]

You are awesome. The macro is working perfectly as i needed. Thanks a ton

[logesh excel]

Hi, The above macro is working fine however i getting a popup message all the time when i run the macro. The message is "There is a large amount of information on the clipboard. Do you want to paste this information into another program later?" and it's asking for Yes or No. To get rid of this i have used Application.CutCopyMode = False but still i couldn't fix it. Is there a way to fix this?

[AlphaFrog]

Hi, The above macro is working fine however i getting a popup message all the time when i run the macro. The message is "There is a large amount of information on the clipboard. Do you want to paste this information into another program later?" and it's asking for Yes or No. To get rid of this i have used Application.CutCopyMode = False but still i couldn't fix it. Is there a way to fix this?

Did you put it just before the copy or just after the paste? I think one or both should fix it.

            With Workbooks.Open(strFile)
                Application.CutCopyMode = False
                .Sheets(1).UsedRange.Copy
                ThisWorkbook.Sheets("Sheet1").Range("A10").PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
                .Close SaveChanges:=False
            End With

[logesh excel]

Oh yeah.. got it where did i messed. Thanks for your help. Now it works fine.