Dynamic File Name and Import of Data

[seanygy1uk]

Hi,

I after some help to ease a procedure I need to carry out.

What I am after is:

in cell A1 it will have the file name I am looking for ie Packing 03.02.2018, this will be in a folder on a network drive

What I want it to do is load a sheet from that workbook to a sheet in this workbook so I can easily see data from the link above.

Any help is much appreciated

 

[Yongle]

Amend fPath to full path to your file
Amend sName to name of sheet to be copied
Amend extension if not xlsx

Place code in Standard module and run from sheet where cell A1 contains the file name

Sub InsertSheetFromWorkbook()
    Const fPath = "[COLOR=#ff0000]Q:\Folder\SubFolder\SubSubFolder[/COLOR]"
    Const sName = "[COLOR=#000080]MySheetName[/COLOR]"
    Dim fName As String, ws As Worksheet, wb As Workbook
    
    Set ws = ActiveSheet
    fName = ws.Range("A1").Value & "[COLOR=#006400].xlsx[/COLOR]"
    Set wb = Workbooks.Open(fPath & "" & fName)
    
    wb.Sheets(sName).Copy before:=ws
    wb.Close False
End Sub