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:

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

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


    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

    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!