Hi guys
I am using the below code to import a specific range of data from another spreadsheet into my current workbook I have open. The below code works perfectly.
However, what I am trying to achieve is to amend the code so that my current Msg box presents the following:
Instead of simply just saying 'BOM Import Successful' I would like it to say 'BOM Import Successful' and then on a new line I'd like to add some text, for e.g. 'x number' of items were not found. X number being the number of occurrences that contain the text 'Item Not Found' in data range J14:J64 (sheet is called 'Dashboard').
However, if the count finds 0 occuraces of 'Item Not Found' in data range J14:J64 then I would like the Msg box to simply say the following:
'BOM Import Successful' and then on a new line underneath 'All items were successfully imported!'
Is anyone able to help amend my code please?
I am using the below code to import a specific range of data from another spreadsheet into my current workbook I have open. The below code works perfectly.
However, what I am trying to achieve is to amend the code so that my current Msg box presents the following:
Instead of simply just saying 'BOM Import Successful' I would like it to say 'BOM Import Successful' and then on a new line I'd like to add some text, for e.g. 'x number' of items were not found. X number being the number of occurrences that contain the text 'Item Not Found' in data range J14:J64 (sheet is called 'Dashboard').
However, if the count finds 0 occuraces of 'Item Not Found' in data range J14:J64 then I would like the Msg box to simply say the following:
'BOM Import Successful' and then on a new line underneath 'All items were successfully imported!'
Is anyone able to help amend my code please?
Code:
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", "I48").Value = sourceSheet.Range("A2", "K48").Value
' Close customer workbook
customerWorkbook.Close
targetSheet.Range("F7").Value = customerFilename
MsgBox "BOM Import Successful!", vbInformation
End Sub
Last edited:
