Sub import()
Dim fileSelected As Boolean, sourceAlreadyOpen As Boolean
Dim thisWkb As Workbook, sourceWkb As Workbook
Dim strSourceFilePath As String, strSourceFileName As String
fileSelected = True
' this section provides the ability to select a source workbook, using a normal file-open pop up
Set thisWkb = ThisWorkbook
' build filter list for file open drop-down
Dim strFilterList As String
strFilterList = "Excel workbooks and templates, *.xls; *.xlt; *.xlsm; *.xlsx; *.xltm; *.xltx; *.xlsb"
strSourceFilePath = Application.GetOpenFilename(strFilterList)
If strSourceFilePath <> "False" Then
strSourceFileName = GetFileName(strSourceFilePath)
If isWorkbookOpen(strSourceFileName) = True Then
Set sourceWkb = Workbooks(strSourceFileName)
sourceAlreadyOpen = True
Else
Set sourceWkb = Workbooks.Open(fileName:=strSourceFilePath)
sourceAlreadyOpen = False
End If
Else
fileSelected = False
End If
' this section processes if a file was selected
If fileSelected Then
Application.StatusBar = "processing"
Dim rngToCopy As Range
With sourceWkb.Sheets("your sheet name here")
Set rngToCopy = .Range(.Range("A4:O8"), .Range("A4:O8").End(xlDown))
End With
rngToCopy.Copy Destination:=thisWkb.Sheets("order data").Range("A4").Select
If Not sourceAlreadyOpen Then sourceWkb.Close savechanges:=False
Call Inv_Warehouse
End If
End Sub
Function GetFileName(filePath As String) As String
Dim StPathSep As String
Dim iFNLength As Integer, i As Integer
StPathSep = Application.PathSeparator
iFNLength = Len(filePath)
For i = iFNLength To 1 Step -1
If Mid(filePath, i, 1) = StPathSep Then Exit For
Next i
GetFileName = Right(filePath, iFNLength - i)
End Function
Function isWorkbookOpen(fileName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next
Set Wkb = Workbooks(fileName)
If Not Wkb Is Nothing Then
isWorkbookOpen = True
End If
End Function