Excel VBA, getting range from an inactive sheet

    This script works fine when I'm viewing the "Temp" sheet. But when I'm in another sheet then the copy command fails. It gives an Application-defined or object-defined error:

    Code
    Sheets("Temp").Range(Cells(1), Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial

    I can use this script instead, but then I have problems with pasting it:

    Code
    Sheets("Temp").Columns(1).Copy
Sheets("Overview").Range("C40").PasteSpecial


    I don't want to activate the "Temp" sheet to get this.


    What else can I do ... ?

    Edited once, last by Carim: Added Code Tags ().

    You need to specify the sheet for the Cells properties too:


    Sheets("Temp").Range(Sheets("Temp").Cells(1), Sheets("Temp").Cells(1).End(xlDown)).Copy

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login