Search code examples
excelvbarangecopy-paste

Range is not correct in excel VBA

I'm trying to make a macro to copy data from excel to another excel (which is template - .xltx).

So i wrote a code:

Option Explicit

Sub GenerujD5()

Dim xWb As Workbook
Dim path As String
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim copylastrow As Long
Dim pasterow As Long

path = "C:\pc2\export\export.xls"

Set wsCopy = Workbooks.Open(path).Worksheets("export")
Set wsDest = ThisWorkbook.ActiveSheet

copylastrow  = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
pasterow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

wsCopy.Range("A2:M" & copylastrow).Copy
wsDest.Range("A5" & pasterow).PasteSpecial xlValues

End Sub

My problems:

It is copying the data, but not into correct cell. Template has 4 rows, so i have to copy them into the 5. row. But it copies into the 55. row.

Solution

  • You calculate the first free row from the destination sheet and write it to the variable pasterow. Assuming that your destination sheet has four lines filled, this will be 5.

    Later you write wsDest.Range("A5" & pasterow).PasteSpecial xlValues. Now VBA will concatenate the string "A5" and the content of your variable pasterow, that is the same as "A5" & 5", and that will result in "A55".

    If you know for sure that you will have always four lines filled, you can get rid of the variable pasterow and simply write

    wsDest.Range("A5").PasteSpecial xlValues

    But the better attempt is to use it and write

    wsDest.Range("A" & pasterow).PasteSpecial xlValues

    As an alternative, you can also write

    wsDest.Cells(pasterow, 1).PasteSpecial xlValues

    or

    wsDest.Cells(pasterow, "A").PasteSpecial xlValues