VBA to open another workbook and copy data - record the location of other workbook

[gheyman]

I have code that allows the user to open another workbook and to copy data from that workbook into the one they currently have open.

Is there a way to modify this code so that when they select the other workbook it "pastes" the address in Sheets("Tracker").Range("C4")?

Sub Get_Data()

Retriev_BOETab Macro
'
'
    ' Retrieve Current Workbook Name
    Dim Mtrl_Resource As Workbook
    Set Mtrl_Resource = ThisWorkbook

    Application.ScreenUpdating = False
    
    ' Open Dialog Box to Select File to Copy From & Save it's Name in a Variable
    MsgBox "Retrieve the 'Material Estimates' tab from the Material Cost Estimates File - Select the MCE File."
    Dim filePicker As Office.FileDialog
    Set filePicker = Application.FileDialog(msoFileDialogFilePicker)
    With filePicker
        .Filters.Clear
        .Title = "Select an Excel File"
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1

        
        Dim selectedFile As String
        
        If .Show = True Then
            selectedFile = .SelectedItems(1)
        End If
    End With
    
    ' Open Selected File & Copy Data
    If selectedFile <> "" Then
        Dim MaterialEstBook As Workbook
        Set MaterialEstBook = Workbooks.Open(selectedFile, ReadOnly:=True)
    
        ' Copy & Paste-Value: BIDDER_INPUT Tab
        Workbooks(MaterialEstBook.Name).Worksheets("BIDDER_INPUT").Range("A1:AC50000").Copy
        Workbooks(Mtrl_Resource.Name).Worksheets("BIDDER_INPUT").Range("A1").PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    
        ' Clear Clipboard
        Application.CutCopyMode = False
    
        ' Close Extra Workbook
        MaterialEstBook.Close SaveChanges:=False
        Set MaterialEstBook = Nothing
    End If

End Sub

Thank You!

 

[kevin9999]

I would be more encouraged to help you if I knew you would respond to help given: Copy a table Column to another workbook if a column name match is found

 

[gheyman]

My apologies. You are absolutely right, I should have responded. No excuses.

 

[kevin9999]

I took the liberty of rewriting your code slightly as I would have it if it was my project, I hope you don't mind

Option Explicit
Sub Get_Data_2()

    ' Retrieve Current Workbook Name
    Dim MaterialEstBook As Workbook, FileName

    ' Open Dialog Box to Select File to Copy From & Save it's Name in a Variable
    MsgBox "Retrieve the 'Material Estimates' tab from the Material Cost Estimates File - Select the MCE File."
    
    FileName = Application.GetOpenFilename _
    (filefilter:="Excel files (*.xl*),*.xl*", MultiSelect:=False)
    If FileName = False Then Exit Sub
    Set MaterialEstBook = Workbooks.Open(FileName, ReadOnly:=True)
    
    ' *** This will put the address of the opened file in Tracker sheet cell C4 ***
    ThisWorkbook.Sheets("Tracker").Range("C4") = Application.ActiveWorkbook.FullName
    
    ' Copy & Paste-Value: BIDDER_INPUT Tab
    MaterialEstBook.Worksheets("BIDDER_INPUT").Range("A1:AC50000").Copy
    ThisWorkbook.Worksheets("BIDDER_INPUT").Range("A1").PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    ' Close Extra Workbook
    MaterialEstBook.Close 0

End Sub

 

[gheyman]

Very much appreciated. Thank you. this worked perfectly

 

[kevin9999]

Happy to help & thanks for the feedback