How to select data from specific excel sheet

Question

I need to select data from the cells on an excel sheet call 'sheet1'

However, when I navigate to a different sheet on my excel document, and Just say I run the macro from the VBA developer page, it takes the data from the sheet I am on, and not 'sheet1'.

Here is my code...

Sub CreateMail()

    Dim objOutlook As Object
    Dim objMail As Object
    Dim rngTo As Range
    Dim rngBody As Range

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

    With ActiveSheet
        Set rngBody = .Range(.Range("B5"), .Range("D5").End(xlDown))
    End With
    rngBody.Copy

    With objMail
        .To = "xxxx"
        .Subject = "Project Update - " & Range("D2") & " on " & Format(Now(), "dd/mm/yyyy")
        .display


    End With
    SendKeys "^({v})", True

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub

Can anyone explain why it will only take the cell values from 'sheet1' when I am on that sheet.

Gareth Gareth · Accepted Answer · 2014-05-21T14:51:48

You are referencing the ActiveSheet within your code which means it will refer to the open sheet in excel on this line:

With ActiveSheet

You need to reference the sheet that you want to get the data from by changing this to:

With Sheets("Sheet1")

So in your code:

Sub CreateMail()

    Dim objOutlook As Object
    Dim objMail As Object
    Dim rngTo As Range
    Dim rngBody As Range

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

    With Sheets("Sheet1")
        Set rngBody = .Range(.Range("B5"), .Range("D5").End(xlDown))
    End With
    rngBody.Copy

    With objMail
        .To = "xxxx"
        .Subject = "Project Update - " & Sheets("Sheet1").Range("D2") & " on " & Format(Now(), "dd/mm/yyyy")
        .display


    End With
    SendKeys "^({v})", True

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub

How to select data from specific excel sheet

1 Answers