Change macro to send as excel file not pdf.

[And180y]

I have the macro below but need it to send the pivot table as an excel file and not a pdf.
I have searched around but not found any clues. Grateful for any help

Please Login or Register  to view this content.

[Logit]

.
http://www.vbaexpress.com/forum/show...able-using-VBA

[And180y]

Hi Logit,
not sure what I was doing with the macro in the link but I couldn't get it to work.

I have managed to cobble together 2 macros and have managed to get it nearly working
with one flaw, it is sending the whole pivot table rather than just what is relevant to the addressee
when a is selected as company it should only send the info shown. Currently the addressee can filter
and see all information. I have attached the parent sheet and an example of what gets sent.
Grateful for any help.

[maniacb]

This solution filters the data page for the company data and creates a workbook based on that filter. It does not use the pivot.

Please Login or Register  to view this content.

[And180y]

Hi Maniacb, thanks for answering and apologies for delay in replying, been a bit under the weather.

My live sheet pulls the data from a much bigger data set, hence the pivot would the above edit to suit the rows and columns I need?

[maniacb]

Andy180y, can you restate your question? For clarity sake, I am attaching the file with the code embedded and you'll see that the resultant workbook has the same information as the pivot. Give it a try

[And180y]

Thanks Maniacb, nearly there, I need to change the columns the macro is picking up data from, is that possible?

[maniacb]

This line selects the lines to be filtered

Please Login or Register  to view this content.

and seen

Please Login or Register  to view this content.

I may be able to help if you share a representative sample workbook

[And180y]

Thanks Maniacb, I would need to knock up an anonymous sheet.
Basically it would filter on the info (company name in column A with info from O,C,N,AC,BC) is that something that would work?

[maniacb]

it would be a lot straight forward if the info you want shared is in consecutive columns(C,N,O,AC,BC). But yes, it's possible. the code would look like something this:

Please Login or Register  to view this content.

[And180y]

Thanks maniacb, I tried changing some of the columns but no matter what I do I get the original output
from your code above. Can you tell me what I am doing wrong?

[And180y]

I think I have made progress, I have linked a new sheet to the columns in my data sheet and now all the columns I need
are next to one another and in order A to F now.

[maniacb]

can you share what changes you tried?

[And180y]

Hi Maniacb I tried changing column letters around.

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time since the thread has advanced. --6StringJazzer

[maniacb]

Please Login or Register  to view this content.

[And180y]

Hi Maniacb, thank for the above but still no joy.
I have constructed a sheet from the actual layout to show you the expected and actual output.
Not sure where it's going wrong. Thanks for helping me with this.

[maniacb]

Here is the updated code section

Please Login or Register  to view this content.

[And180y]

Maniacb thanks so much. Is it possible that a differing version of Excel could be an issue as still not getting the right output?

[maniacb]

It shouldn't. Here is the working file

[And180y]

Thanks Maniacb, all looked good on the working file above but when I run the macro I get this error. Attachment 707261
and it debugs to this line.

Please Login or Register  to view this content.

[maniacb]

Updated that line to this

Please Login or Register  to view this content.

And added line to clear memory.

Please Login or Register  to view this content.

[And180y]

Maniacb, thankyou so much it works perfect. :-)

[And180y]

All working perfect. I've been trying to save this to somewhere other than my flash drive F:\
Is it possible to save the temp file to my documents on C:\?

[maniacb]

All you need to do is to replace "F:\" with say "C:\Users\George\Desktop\docs\"

[And180y]

Thanks Maniacb, one last query I changed .display to .send thinking it would send the emails without displaying and having to click them
but it said something about not being defined. Any ideas on that one. Thanks again for help so far. :-)

[maniacb]

Here is updated code to include a picture instead of an attachment.

Please Login or Register  to view this content.

[maniacb]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon (Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.