Copy and paste data specified range between workbooks using VBA

Sub ImportData()
Dim SourceWB As Workbook, TargetWB As Workbook
Dim SourceWs As Worksheet, TargetWs As Worksheet
Dim FolderPath As String, Filter As String, Caption As String, SourceFName As String
Dim CopyRng As Range, PasteRng As Range
Dim LCol As Long

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Uploading Data !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Define Variables
Set TargetWB = ThisWorkbook
Set TargetWs = TargetWB.Sheets(1)
FolderPath = Application.ThisWorkbook.Path
ChDir FolderPath
Filter = "Text files (*.xl*),*.xl*"
Caption = "Please Select an input file "
SourceFName = Application.GetOpenFilename(Filter, , Caption)
Set SourceWB = Application.Workbooks.Open(SourceFName, Format:=xlDelimited, Local:=True)
Set SourceWs = SourceWB.Sheets(1)
Set CopyRng = SourceWs.Range("D3:E9999")
LCol = TargetWs.Cells(9, Columns.Count).End(xlToLeft).Column
Set PasteRng = TargetWs.Range(TargetWs.Cells(9, LCol + 1), TargetWs.Cells(9, LCol + 1))

'Copy Data
If TargetWs.Range("A9") = "" Then
    CopyRng.Copy
    TargetWs.Range("A9").PasteSpecial xlPasteValuesAndNumberFormats
Else
    CopyRng.Copy
    PasteRng.PasteSpecial xlPasteValuesAndNumberFormats
End If

Application.CutCopyMode = False

'Close Source Workbook
Application.DisplayAlerts = False
SourceWB.Close SaveChanges:=False
Application.DisplayAlerts = True

TargetWs.Activate
TargetWs.Range("A8").Select

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Select allOpen in new window