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

S

StevieMP

Board Regular
Joined
Sep 28, 2021
Messages
70
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
501
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
305
Peter_SSs
Peter_SSs
F
  • Question
How to get optionbutton values from two different groups
Replies
2
Views
512
NoSparks
N
D
  • Solved
Populate a "Despatch Note" from a Separate "Project Tracker" Workbook when a Unique Job Number (UJN) is input.
Replies
5
Views
637
Kerryx
K
A
  • Solved
Retrieve multiple values using VBA from dictionary
Replies
4
Views
1K
ajl344
A

Forum statistics

Threads
1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

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