Update import data button to include name of worksheet

[Cuzzaa]

Hi Everyone

I'm new here and this is my first post! I really hope someone is able to help.

I am using the below vba code for clicking a button to prompt the user with selecting a separate worksheet and then it automatically copies a set range from the new selected worksheet and pastes into a set range into my current worksheet. The below code works absolutely fine, but I was wondering if someone could help with what code I need to add in the feature of pasting the name of the spreadsheet the user selects and the directory where this is saved, i.e. cell I11 will read 'Z:\Customer Drive\Worksheetname.xls'.

Please can someone help me? Thanks so much in advance!

Sub BOMTest()


' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook


' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please select the BOM "
customerFilename = Application.GetOpenFilename(filter, , caption)


Set customerWorkbook = Application.Workbooks.Open(customerFilename)


' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)


targetSheet.Range("F14", "L48").Value = sourceSheet.Range("A16", "G48").Value


' Close customer workbook
customerWorkbook.Close


MsgBox "BOM Import Successful!", vbInformation
End Sub

 

[RoryA]

Welcome to the forum.

Add this:

targetsheet.range("I11").Value = customerFilename

 

[Cuzzaa]

Welcome to the forum.

Add this:

targetsheet.range("I11").Value = customerFilename

RoryA,

Thanks so much for the quick response, that's perfect. Feel free to close this thread. I'm very grateful.

 

[RoryA]

Glad to help.

We don't close threads here, BTW.