Excel VBA - Put value in destination cell in another worksheet

Posted on 2011-05-09
Last Modified: 2012-05-11
I'm trying to put a value into a cell in another worksheet (same workbook) without selecting that other wks.
My code below is not working...

Sub M_PutValueInOtherWks()
    Dim vDestRow As Integer
    Dim vDestCol As Integer
    vDestCol = 4
    vDestRow = 114
       Sheet3.Range(Cells(vDestRow, vDestCol), Cells(vDestRow, vDestCol)).Value = "XYZ"

'// this alternative line of code doesn't work either...
        Sheet3.Cells(vDestRow, vDestCol).Value = "XYZ"

End Sub

Question by:AndresHernando

    Author Comment

    BTW, I'm calling this from Sheet1 code (not module)
    LVL 43

    Accepted Solution

    Sheet3.Range(Cells(vDestRow, vDestCol).Address & ":" & Cells(vDestRow, vDestCol).Address).Value = "XYZ"

    Open in new window

    LVL 85

    Expert Comment

    by:Rory Archibald
    In what way does:
    Sheet3.Cells(vDestRow, vDestCol).Value = "XYZ"
    not work? It should assuming that Sheet3 is the correct code name (not sheet caption) and that the sheet is not protected.
    LVL 19

    Expert Comment

    you can use

    Sheet3.range("D114")= "XYZ"
    LVL 19

    Expert Comment


    Sheets(3).range("D114") = "XYZ"

    or even

    Sheets("Sheet3").range("D114") = "XYZ"

    Author Closing Comment

    Worked like a charm!  Thanks!!!  --Andres

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now