Referencing a Data Range in Sheet2 whilst being in Sheet3

[StevieMP]

I have a list of emails in 2 columns in Sheet2 -
Column A Column B

To:	Cc:
abcd@hotmail.com	ijkl@hotmail.com
efgh@hotmail.com	mnop@hotmail.com

I have the following code which works whilst I am in Sheet2 where the emails are listed, as it creates an email and puts the emails in column A as the main email recipients and column B as the Cc'd :

Dim OutApp As Object
Dim OutMail As Object
Dim str1, str2 As String
Dim edress As String
Dim subj As String
Dim Worksheet As Range
Dim ThisWorkbook As Object
Dim sMail_ids As String
Dim sMail_ids2 As String
Dim myDataRng As Range
Dim myDataRng2 As Range

Set myDataRng = Range("A2:A3" & Cells(Rows.Count, "A").End(xlUp).Row)
Set myDataRng2 = Range("B2:B3" & Cells(Rows.Count, "B").End(xlUp).Row)

For Each cell In myDataRng
If Trim(sMail_ids) = "" Then
sMail_ids = cell.Offset(0, 0).Value
Else
sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(0, 0).Value
End If
Next cell

Set myDataRng = Nothing ' Clear the range.


' Run a loop to extract email ids from the 2nd column.
For Each cell In myDataRng2
If Trim(sMail_ids2) = "" Then
sMail_ids2 = cell.Offset(0, 0).Value
Else
sMail_ids2 = sMail_ids2 & vbCrLf & ";" & cell.Offset(0, 0).Value
End If
Next cell

Set myDataRng2 = Nothing ' Clear the range.


I know this is a really stupid question - I would like to execute the code whilst being in Sheet3, however when doing so it references the information in Sheet3. Can you assist please?

Thank you in advance

 

[PeteWright]

Just put the Sheet Name in front and you're done. But make sure you use the right Name:

Taking my example shown in the screenshot you could do the following.

Method 1: Sheet1.Range()

Method 2: Sheets("Sheet X").Range()

 

[MARK858]

If it is the sheets tab name

Set myDataRng = Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
Set myDataRng2 = Sheets("Sheet2").Range("B2:B" & Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row)

 

[StevieMP]

If it is the sheets tab name

Set myDataRng = Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
Set myDataRng2 = Sheets("Sheet2").Range("B2:B" & Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row)

Thank you so so much!!!!
That works perfectly

 

[MARK858]

You're welcome