Sheet.Select + Copy/Paste won't work [Excel 2016]

Question

I have a macro that copies some columns from a BD Sheet and pastes in another sheet.

I've got this code working in Excel 2007, but I've encountered an issue Selecting a Sheet, then copy/paste in Excel 2010 and later. It seems the problem is not in my .Select. It appears to be in the PasteSpecial() that automatically selects the with Sheet() and executes other .copy() without going back to de previous sheet (the screen blinks every pasteSpecial) - I don't know if I was clear enough. [sometimes it works fine, especially using debugger]

Code

Const BD_SHEET As String = "Estrategia"
Const PRICE_SHEET As String = "Precos"

Public Sub Execute()
    ....

    actualCalculate = Application.Calculation
    Application.Calculation = xlCalculationManual

    LoadPrices()

    Application.Calculate
    Application.Calculation = actualCalculate
End Sub


Private Sub LoadPrices()
    Dim lastSheet As Worksheet

    Set lastSheet = ActiveSheet
    Sheets(BD_SHEET).Select
    lastRow = [A1000000].End(xlUp).row

    With Sheets(PRICE_SHEET)
        Range(Cells(2, 2), Cells(lastRow, 2)).Copy
        .[A2].PasteSpecial xlPasteValues  '<---- Working

        Range(Cells(2, 7), Cells(lastRow, 7)).Copy
        .[B2].PasteSpecial xlPasteValues  '<---- Working

        Range(Cells(2, 9), Cells(lastRow, 10)).Copy '<---- Error!
        .[C2].PasteSpecial xlPasteValues

        Range(Cells(2, 12), Cells(lastRow, 12)).Copy '<---- Error!
        .[E2].PasteSpecial xlPasteValues
    End With

    lastSheet.Select
End Sub

I can remove .Select and add Set theSheet = Sheets(BD_SHEET) but the code is going to be durty.

Exemple:

...
Set lastSheet = ActiveSheet
Set bdSheet = Sheets(BD_SHEET)
lastRow = [A1000000].End(xlUp).row

With Sheets(PRICE_SHEET)
    bdSheet.Range(bdSheet.Cells(2, 2), bdSheet.Cells(lastRow, 2)).Copy
    .[A2].PasteSpecial xlPasteValues
End With
...

score 2 · Accepted Answer · edited May 23 '17 at 10:28

but the code is going to be durty.

That is because you are doing it the wrong way

Instead of

With Sheets(PRICE_SHEET)
    bdSheet.Range(bdSheet.Cells(2, 2), bdSheet.Cells(lastRow, 2)).Copy
    .[A2].PasteSpecial xlPasteValues
End With

Do this

With bdSheet
    .Range(.Cells(2, 2), .Cells(lastRow, 2)).Copy
    Sheets(PRICE_SHEET).[A2].PasteSpecial xlPasteValues  '<---- Working
End With

Also never use Hardcoded values to find the last row. You may see This on how to calculate the last row.

Also

Range1.Copy
Range2.PasteSpecial xlPasteValues

can be written as

Range2.Value = Range1.Value

Applying the above, I have re-written your code. Is this what you are trying? (Untested)

Private Sub LoadPrices()
    Dim wsCopyFrm As Worksheet, wsCopyTo As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set wsCopyFrm = ThisWorkbook.Sheets(BD_SHEET)
    Set wsCopyTo = ThisWorkbook.Sheets(PRICE_SHEET)

    With wsCopyFrm
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Set rng = .Range(.Cells(2, 2), .Cells(lastRow, 2))
        wsCopyTo.Range("A2").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value

        Set rng = .Range(.Cells(2, 7), .Cells(lastRow, 7))
        wsCopyTo.Range("B2").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value

        Set rng = .Range(.Cells(2, 9), .Cells(lastRow, 10))
        wsCopyTo.Range("C2").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value

        Set rng = .Range(.Cells(2, 12), .Cells(lastRow, 12))
        wsCopyTo.Range("E2").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
    End With
End Sub

Sheet.Select + Copy/Paste won't work [Excel 2016]

1 Answers1