Modify VBA to import Text File into new rows in a workbook sheet

[danielalvz]

hello Im trying to modify a VBA code, right now I have a code working that open a text file as workbook, then it copies the ranges from the active workbook into thisworkbook, the only issue is that I will continue to import into this workbook and I need it to import into the next row, and this bit of coding to copy the desire range into a new row is not working out for me, see current code below. Thanks in advance

Sub Get_Summary()

Dim FileToOpen As Variant
Dim wsMaster As Worksheet
Dim wbTextImport As Workbook
Dim destLastRow As Long

Application.ScreenUpdating = False

Set wsMaster = ThisWorkbook.Sheets(15)

destLastRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row
If destLastRow < 1 Then destLastRow = 1

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Text Files (*.txt*),*txt*")

If FileToOpen = False Then
MsgBox "No File Selected"

Else

Workbooks.OpenText _
Filename:=FileToOpen, _
StartRow:=2, _
DataType:=xlDelimited, _
Tab:=True

Set wbTextImport = ActiveWorkbook


wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A1" & destLastRow + 1)

wbTextImport.Close False

End If

Application.ScreenUpdating = True

Worksheets("Main Sheet").Activate

End Sub

 

[Joe4]

Try changing this line:

wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A1" & destLastRow + 1)

to this:

wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A" & destLastRow + 1)

Note that you were doing.l
Let's say that "destLastRow" was 20 (so the last row with data in column A on that sheet was row 20.
You then want to paste to line 21. So adding one to that variable accomplishes that.
However, you were NOT attaching that to column "A", you were attaching it to "A1", meaning you would be pasting it to row A121, not A21.

 

[danielalvz]

Try changing this line:

wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A1" & destLastRow + 1)

to this:

wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A" & destLastRow + 1)

Note that you were doing.l
Let's say that "destLastRow" was 20 (so the last row with data in column A on that sheet was row 20.
You then want to paste to line 21. So adding one to that variable accomplishes that.
However, you were NOT attaching that to column "A", you were attaching it to "A1", meaning you would be pasting it to row A121, not A21.

thanks