Excel Macro question

[texasguy66]

Hello everyone.

My excel template has a macro in it to create an email in outlook to send out information from the workbook. One of the ranges to capture a range of data to copy to the body of the email is

Set rng2 = Sheets("Pivot").Range("D4:M17")

Is there a way that I can have input in the spreadsheet that I can quickly change this range as needed? Currently I go into the macro and manually update.

Thanks in advance.

 

[Joe4]

One easy way would be to use a named range, and then just refer to that name in VBA.
Then all you have to do on your Excel sheet is change the named range, and it will filter through to your VBA.
See: Refer to Named Ranges

 

[Tetra201]

Or maybe

Set rng2 = Application.InputBox(Prompt:="Select a range", Type:=8)

 

[texasguy66]

Thank you Tetra201. That works perfectly!

 

[Tetra201]

Glad to have helped.