Send only Ranges selected with Userform - Not the entire sheet

[JJFletcher]

Hello All,

I have uploaded a workbook that has a range of data in cells from Column B to Column N

I have created a Userform and when selecting the desired months that is to be emailed - the entire sheet is being sent

Any thoughts


Best Regards,

John

[JJFletcher]

I have been looking at this code and seeing if it will work... it selects the ranges needed but fails at the select range at the end of the code for the email... any idea's?

Please Login or Register  to view this content.

Any Thoughts,

John

[JJFletcher]

Is this a question that has no answer??

Is this the wrong area for this question... Can someone see if this code is appropriate and why it does not function correctly?

Please Login or Register  to view this content.

Best Regards,

John

[bakerman2]

This does the same as your code.

Please Login or Register  to view this content.

But what is to happen with selection because now only SelectRange is selected and then ...nothing.

[JJFletcher]

But what is to happen with selection because now only SelectRange is selected and then ...nothing. This is a great question... That was the trouble I was having with the other code -

Instead of the selected ranges being sent - The entire sheet was being sent..

updated book attachment:



Any Thoughts

John

[bakerman2]

Thoughts Yes but no time left to put it in code.(have to work tonight)

Instead of selecting ranges hide the columns you don't need. Copy visible cells to temporary sheet then use mailcode to send temporary sheet.
Tried this manually and works perfect. If you have time to wait I can put it together by tomorrow but for now have to sign off.
In your example workbook add a new sheet (place it after sheet1) hide a few columns on sheet1 and try next code.

Please Login or Register  to view this content.

[NoSparks]

Copy visible cells to temporary sheet then use mailcode to send temporary sheet.
Tried this manually and works perfect.

@ John

Isn't that pretty much right along the same lines as snb's suggestion at one of your cross postings.

[JJFletcher]

Hi Backerman,

I will look forward to your thoughts tomorrow on this... I will in the man time look at this modification..

I will check in tomorrow and see if the current code modification you come up with works perfectly

Thanks in advance

John

[JJFletcher]

I have just purchased some Commercial Services... Who would like to assist in this issue so I can get a resolution to this and move forward with my project?

The last book uploaded has a working code to select the ranges - yet the entire sheet is being sent via email instead of the selected ranges...

The desire is to have only the selected ranges be emailed and once completed the sheet refreshes back to normal and the userform will close

Best Regards,

John

[jaslake]

Hi John

Your PM quota is full. Delete some items so we can PM. Let me know when you've done so.

[JJFletcher]

Cleared PM

[jaslake]

Hi John

You'll need to PM a Forum Moderator or Administrator and ask them to close this Tread as you're posting in Commercial Services.

[bakerman2]

@jaslake

Is this thread still open for replies because I have the solution ready and waiting ?

[JJFletcher]

Bakerman,

it is still open... I would like to see your suggestion...

You indicated hiding columns and sending selected data to a second sheet - I am wondering how well this will work

John


I have been speaking to jaslake but have not reviewed any suggestions as not able to successfully post this issue in the Commercial services

[jaslake]

Hi bakerman2

Far as I can tell this Thread has not been closed. Offer up solutions for the OP.

[JJFletcher]

This is what was sent to the moderator...

Oh what is an OP???

I am trying to post a thread to the commercial services so I can get assistance and this is the message I get:

http://www.excelforum.com/showthread.php?t=1142915


JJFletcher, you do not have permission to access this page. This could be due to one of several reasons:
1.Your user account may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
2.If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Can you please assist and advise?

[jaslake]

Hi John

OP is the "Original Poster"...that be you.

[JJFletcher]

Thanks John

[JJFletcher]

Hi backerman2

What solution do you have?

John

[bakerman2]

Please Login or Register  to view this content.

[JJFletcher]

Hi Bakerman2,

When trying to run the code the following error highlighted in yellow:

Sheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Copy Sheets(2).Range("B1")

[bakerman2]

Did you create second sheet. (place after first sheet).
Code can also be altered to create temporary sheet and delete afterwards if that better suits your needs.

[JJFletcher]

I did create the second sheet - pls see attached.... I really like that suggestion - I do not want the user to be thinking there is a second sheet to interact with.

The second book attached selects the ranges desired but when sending the email it sends the entire sheet instead of the selected ranges...


Going to dinner - be back soon

[bakerman2]

Here you go.

[JJFletcher]

Checking this out now - will get back to you...

Thanks so much in advance...

John

[JJFletcher]

Hi backerman2,

PM sent - did you see that?

John

[bakerman2]

Already answered.