How can I use LastRow on a range function?

Question

So I want to copy values from a certain range of cells from worksheet 1-workbook A to worksheet 1- workbook B .

I want to copy everything from the source worksheet: more specifically, every cell that has a value on it.

On the destination worksheet, there are specified cells for the values on source worksheet.

this is my code so far (it's bad, but i'm a noob at VBA!) :

Sub CopyRangeofCells()
Dim x As Workbook
Dim y As Workbook



Set x = Workbooks.Open("C:\test\template.xlsx")
Set y = Workbooks.Open("C:\test\finalfile.xlsx")


x.Sheets("RDBMergeSheet").Range("A1").Copy


y.Sheets("CW Fast").Range("A1").PasteSpecial

'Close x:
x.Close

End Sub

On my range, I want to do something like Range("A1:LastRow") or anything of the sort. How do I do it? Can I create a lastrow variable and then do ("A1:mylastrowvariable") ??

Hope you can help! VBA is so confusing to me, give me Java all day long! :P

Answer 1

Something like this:

Sub CopyRangeofCells()
Dim x As Workbook
Dim y As Workbook
Dim LastRow as Long
Dim LastRowToCopy as Long

Set x = Workbooks.Open("C:\test\template.xlsx")
Set y = Workbooks.Open("C:\test\finalfile.xlsx")

LastRowToCopy = x.Sheets("RDBMergeSheet").Cells(x.Sheets("RDBMergeSheet").Rows.Count, "A").End(xlUp).Row
x.Sheets("RDBMergeSheet").Range("A1:A" & LastRowToCopy).Copy
'copy from A1 to lastrow
LastRow = y.Sheets("CW Fast").Cells(y.Sheets("CW Fast").Rows.Count, "A").End(xlUp).Row + 1 'find the last row 
y.Sheets("CW Fast").Range("A" & LastRow).PasteSpecial xlPasteValues
'paste on the lastrow of destination + 1 (so next empty row)
x.Close
End Sub

Answer 2

Let's do it step-by-step:

Sub CopyRangeofCells()

Dim x As Workbook
Dim y As Workbook
Dim LastRow As Long

Set x = Workbooks.Open("C:\test\template.xlsx")
Set y = Workbooks.Open("C:\test\finalfile.xlsx")

With x.Sheets("RDBMergeSheet")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' get last row with data in column "A"

    .Range("A1:A" & LastRow).Copy
End With

y.Sheets("CW Fast").Range("A1").PasteSpecial xlPasteValues

'Close x:
x.Close

End Sub