Copy cells from a Worksheet to another Worksheet (from different Workbooks and first empty cell)

Question

I am trying to copy the content from a user opened worksheet to another worksheet from another Workbook. The code below do works, but it doesnt select the first empty cell from the WS_REL sheet, and overwrite all the data contained. So far, I have this:

Sub Importar_Dados()

Dim vTemp As Variant
Dim WB_TOA As Workbook, WB_REL As Workbook
Dim WS_TOA As Worksheet, WS_REL As Worksheet

Set WB_REL = ActiveWorkbook
Set WS_REL = WB_REL.Sheets("Planilha2")

vTemp = Application.GetOpenFilename("Excel-files,*.xlsx", _
    1, "Selecione o relatório gerado pelo TOA", , False)

If TypeName(vTemp) = "Boolean" Then Exit Sub
Workbooks.Open vTemp

Set WB_TOA = Workbooks.Open(vTemp)
Set WS_TOA = WB_TOA.Sheets("Page 1")

WS_TOA.Cells.Copy WS_REL.Cells

End Sub

Thank you!

A.S.H A.S.H · Accepted Answer · 2017-04-24T14:17:30

First, you dont need to open the other workbook twice. Remove the line

Workbooks.Open vTemp

Then, to append without overwriting, you need to find the last non-empty cell in your destination sheet. Try it like (for example):

WS_TOA.usedRange.Copy WS_REL.Range("A999999").End(xlUp).Offset(1)

If you dont have a column that is sure to have data in all rows, use this:

WS_TOA.usedRange.Copy WS_REL.Range("A" & WS_REL.Cells.Find("*", , , , xlByRows, xlPrevious).Row+1)

This finds you the first non-empty row.

Copy cells from a Worksheet to another Worksheet (from different Workbooks and first empty cell)

1 Answers