Copy and Paste Data from CSV file to first blank row in worksheet of second workbook

[KayJay0618]

I have a workbook (BoardTime.xlsx) tracking board member time. Each board member will send me a CSV file with their information for a period of time (CSVExport.csv). I need to copy the data from columns A through E from CSVExport.csv starting in cell A2 to a worksheet in BoardTime.xlsx named Time and I need the data to be pasted in the first blank row of the Time worksheet. I've got the following code and it works like a charm EXCEPT that it doesn't paste to the first blank row - it overwrites existing starting in cell A2.

Code:
Sub TimeImport()
'
' Copy data from CSVExport file to BoardTime Reported Time Sheet first blank row
'
Dim timetrack As Workbook, timetrackname As String, timesheet As Worksheet, targetpath As String
Dim Hours As Workbook, Hoursimportname As String, Hoursimportpath As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False


timetrackname = ThisWorkbook.Name
Set timetrack = Workbooks(timetrackname)
targetpath = Application.Workbooks(timetrackname).Path
Set data = timetrack.Worksheets("Time")
importname = "\CSVExport.csv"
importpath = ThisWorkbook.Path




Dim copyRange As Range
Dim lr As Long
Dim newlr As Long


Set Import = Workbooks.Open(ThisWorkbook.Path & "\CSVExport.csv")


Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


' Paste the data in columns A through E to the last row of data


Import.Worksheets(1).Range("A2:E" & Lastrow).Copy
data.Range("A2:E" & Lastrow).PasteSpecial (xlPasteValues)


Import.Close


data.Range("A2").Select
Worksheets("Time").Activate


End Sub

 

[Joe4]

You can dynamically find the next blank row on the sheet you want to paste to like this:

Dim nextRow as Long
nextRow = Cells(Rows.Count,"A").End(xlUp).Row + 1

When you paste, you usually only need to denote the first cell you are pasting to, so that reference would be something like:

Range("A" & nextRow)

 

[KayJay0618]

This doesn't work. It copies to row 14 the first time when rows 2 through 13 are blank and then when I try to copy and paste another person's time, it copies it above the other records and sometimes wipes out one of the records previously pasted.

 

[Joe4]

This doesn't work. It copies to row 14 the first time when rows 2 through 13 are blank and then when I try to copy and paste another person's time, it copies it above the other records and sometimes wipes out one of the records previously pasted.

Sounds like you might not be doing something correct. Note the following things:
- Make sure you are using the correct column to find the last row
- Make sure you are either on, or referencing, the correct sheet
- If you need to do this multiple times, you will either need to recalculate the last row every time, or add one to the count every time

 

[KayJay0618]

I found a solution. I could not get the code to work so I tried several different options I found on other threads in another service and finally found one that worked. Thank you.