Search code examples
excelvbaexport-to-excelexcel-365

How to import to next empty cell / row


The code I have below works fine for importing a selected CSV file. But I want to have it so the next CSV file I import will be pasted in the next empty cell in row "U" (Basically adding on to the list rather than replace). I have googled and googled and googled. I have found hundreds of "solutions" to this, but all of them makes my computer scream "error!". Really on the verge of giving up here, and I am certain the solution is something obvious ;_;

Using Office 365 if that makes any difference.

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
Sheets(2).Unprotect Password:="123"


ChDrive "D:\"
    
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File", FileFilter:="Excel Files (*.csv*),*csv*")
    
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen, Local:=True)
         
        OpenBook.Sheets(1).Range("A:AE").Copy
        ThisWorkbook.Worksheets(2).Range("U1").PasteSpecial xlPasteAll
              
        Application.CutCopyMode = False
        Application.CutCopyMode = True
        OpenBook.Close False

    End If
    
Application.ScreenUpdating = True
Sheets(2).Protect Password:="123"
End Sub```

Solution

  • If you want to paste into the next empty row in Col U then:

    ThisWorkbook.Worksheets(2).Cells(Rows.Count, "U").End(xlUp).Offset(1).PasteSpecial xlPasteAll
    

    In order to do that though, you need to not copy the whole column from the source file, since it won't "fit" if pasted anywhere but Row 1

    With OpenBook.Sheets(1)
         Application.Intersect(.UsedRange, .Range("A:AE")).Copy
    End With