Copy paste range (using set values) to different workbook

Question

I have two workbooks, wb and wb2. What I want to do,

Copy data from sheet Nov to sheet Nov Temp in wb // this part, code is working.
Copy specific range in wb to wb2. // I try to use Rng to set value but it didn't work possibly due to the way I address my range. Rng in (myRange = sht.Rng.Value) is highlighted for

compile error; method or data member not found.

Please help...

Option Explicit

Sub cont()
    Application.Volatile

    Dim sht As Worksheet
    Dim myRange As Variant
    Dim Rng As Range
    Dim Lastrow, ecol, eRow As Integer
    Dim station As String
    Dim wb As Workbook, wb2 As Workbook

    Set wb = ActiveWorkbook
    wb.Sheets("Nov").Activate

    eRow = Cells(Rows.Count, 2).End(xlUp).Row
    station = Range("B2").Value
    Range(Cells(2, 2), Cells(eRow, 2)).Copy
    MsgBox "Transfer data for station: " & station

    On Error GoTo 0

    wb.Sheets("Nov Template").Activate
    Set sht = ActiveWorkbook.Sheets("Nov Template")
    ecol = sht.UsedRange.Columns.Count

    sht.Range(Cells(1, 2), Cells(eRow, 2)).PasteSpecial xlPasteValues
    With ActiveSheet.UsedRange
        Lastrow = .Rows(.Rows.Count).Row
        Set Rng = Range(Cells(1, "C"), Cells(Lastrow, ecol))
        myRange = sht.Rng.Value
    End With

    Workbooks.Open "G:\Mean2std\Merge NDj (2).xlsm"
    Set wb2 = ActiveWorkbook
    wb2.Worksheets("GM").Range("B3:AO32").Value = myRange
    wb2.Close SaveChanges:=True
End Sub

Side note: (1) `On Error GoTo 0` is completely useless here. (2) You should read and follow the whole guide to [VBA Best Practices](http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices) which will sort out many issues if you follow it strictly. (3) `Dim Lastrow, ecol, eRow As Integer` only declares the last variable the others remain `Variant` and never use `Integer` use `Long` instead: `Dim Lastrow As Long, ecol As Long, eRow As Long` — Pᴇʜ, May 17 '17 at 06:43
@Peh, my intention of using On error Goto 0 is to provide escape when applying this code on loop. Thank you I'll go through your suggestion. — Siti Sal, May 17 '17 at 16:06
@Peh, could you explain your 3) sentence.."only declares the last variable the others remain Variant" ? and whats the importance using long instead of integer? — Siti Sal, May 17 '17 at 16:09
What I mean is that `Dim Lastrow, ecol, eRow As Integer` is exactly the same as `Dim Lastrow As Variant, ecol As Variant, eRow As Integer`. And using `Integer` for counting rows is no good idea because `Integer` can handle numbers up to 32767 but Excel has rows up to 1048576. Generally we can [conclude that using integer over long has no advantages](http://stackoverflow.com/a/26409520/3219613) and that it is good practice never to use `Integer` except for 16bit systems. For `On Error …` read [here](http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/5411/error-handling). — Pᴇʜ, May 18 '17 at 06:05

score 0 · Accepted Answer · answered May 17 '17 at 16:27

Now it works, remove myRange and solely use Rng.

Option Explicit

Sub cont()
    Application.Volatile

    Dim sht As Worksheet
    'Dim myRange As Variant
    Dim Rng As Range
    Dim Lastrow, ecol, eRow As Long
    Dim station As String
    Dim wb As Workbook, wb2 As Workbook

    Set wb = ActiveWorkbook
    wb.Sheets("Nov").Activate

    eRow = Cells(Rows.Count, 2).End(xlUp).Row
    station = Range("B2").Value
    Range(Cells(2, 2), Cells(eRow, 2)).Copy
    MsgBox "Transfer data for station: " & station

    wb.Sheets("Nov Template").Activate
    Set sht = ActiveWorkbook.Sheets("Nov Template")
    ecol = sht.UsedRange.Columns.Count

    sht.Range(Cells(1, 2), Cells(eRow, 2)).PasteSpecial xlPasteValues
    With ActiveSheet.UsedRange
        Lastrow = .Rows(.Rows.Count).Row
        Set Rng = Range(Cells(1, "C"), Cells(Lastrow, ecol))
        'myRange = sht.Rng.Value
    End With

    Workbooks.Open "G:\Mean2std\Merge NDj (2).xlsm"
    Set wb2 = ActiveWorkbook
    'wb2.Worksheets("GM").Range("B3:AO32").Value = myRange
    wb2.Worksheets("GM").Range("B3:AO32").Value = Rng.Value
    wb2.Close SaveChanges:=True
End Sub

Copy paste range (using set values) to different workbook

1 Answers1