Selection of a Range from Sheet1 based on an input on Sheet2

S

StevieMP

Board Regular
Joined
Sep 28, 2021
Messages
73
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi There,
I have an Excel workbook and I would like to have some VBA code that when executed looks at the information input into tab Sheet2, Column E, Row 7, Cell E7:

Sheet2
ABCDEFGHIJKLMN
Header1Header2Header3Header4Header5Header6Header7Header8Header9Header10Header11Header12Header13Header14
data1INFO

Here marked as "INFO", and then based on what is entered, selects a named range called "Information1" in tab Sheet1 to copy and paste the range into an email and then loops to the next empty line and stops once completed.

Then, if the next line in Sheet2, Column E, Row 8, Cell E8 has on the next execution INFO3 input, the code selects the next named range in tab Sheet1 called "Information3"

Sheet1
ABCDEFGHIJKLMNOPQRS
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3
Information 1Information 1Information 1Information 1Information 1Information 2Information 2Information 2Information 2Information 2Information 3Information 3Information 3Information 3Information 3

Thank you in advance.
Steve
 
Sort by date Sort by votes
I can't reproduce the error. It works for me even with only one email address in column A. I forgot to mention to also change the letters in red and blue in the code below:
Rich (BB code): 
.to = Join(Application.WorksheetFunction.Transpose(addrWS.Range("A2", addrWS.Range("A" & Rows.Count).End(xlUp)).Value), ";")
.cc = Join(Application.WorksheetFunction.Transpose(addrWS.Range("B2", addrWS.Range("B" & Rows.Count).End(xlUp)).Value), ";")
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
mumps said:
This is what Case "LUX" would look like:
Rich (BB code): 
Case "LUX"
                    Set rng = srcWS.Range("A5:P" & lRow)
                    Set OutMail = OutApp.CreateItem(0)
                    With OutMail
                        If lRow2 > 2 Then
                            .to = Join(Application.WorksheetFunction.Transpose(addrWS.Range("A2", addrWS.Range("A" & Rows.Count).End(xlUp)).Value), ";")
                            .cc = Join(Application.WorksheetFunction.Transpose(addrWS.Range("B2", addrWS.Range("B" & Rows.Count).End(xlUp)).Value), ";")
                        Else
                            .to = addrWS.Range("A2")
                            .cc = addrWS.Range("B2")
                        End If
                        .Subject = srcWS.Range("A3")
                        .HTMLBody = RangetoHTML(rng)
                        .Display
                    End With
Follow the range pattern (in red and blue) and modify all the other cases.
Click to expand...
Hi Mumps,
Just redid it and yes it's worked fine.
Thank you once again. I've learnt some more on the VBA which is greatly appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Tab Selection from Userform based on Date range
Replies
5
Views
161
campbell1093
C
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
681
mumps
mumps
vinzent
  • Solved
Get the PN from one cell with information if this PN appear in the list in another tab
Replies
3
Views
324
Peter_SSs
Peter_SSs
F
  • Question Question
How to get optionbutton values from two different groups
Replies
2
Views
569
NoSparks
N
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
239
krunal123
K

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top