My email button is working perfectly, I am going to generate a new question
about copying as text instead of the whole sheet (copying the whole pivot
table is a large file) but the program is working perfect, thank you for all
of your help.

"Tom Ogilvy" wrote:

Height and width are properties of the entirerow and entirecolumn. If you
want to copy that, you would need to do a

rng.Copy
ActiveSheet.Range("A1").PasteSpecial xlValues
rng.parent.cells.copy
Activesheet.Cells.PasteSpecial xlFormats

--
Regards,
Tom Ogilvy


"Josh Johansen" wrote in message
...
After messing around with this button, is there a way to leave the subject
blank as well so they can add in their own? Also is there a way when copy
and pasting to leave the cell formats the same, basically the cell height
and
width. Thanks again, this is helping me a ton!

"Tom Ogilvy" wrote:

I was using the wrong form of PasteSpecial - mental glitch. This worked
for
me:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = ActiveSheet.PivotTables(1).TableRange2
Workbooks.Add Template:=xlWBATWorksheet
rng.Copy
ActiveSheet.Range("A1").PasteSpecial xlValues
ActiveSheet.Range("A1").PasteSpecial xlFormats
ActiveWorkbook.SendMail Subject:="Myfiles",
"
ActiveWorkbook.Close SaveChanges:=False


End Sub

If you want to include 8 rows above it

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = ActiveSheet.PivotTables(1).TableRange2
set rng = range(rng(1).offset(-8,0),rng)
Workbooks.Add Template:=xlWBATWorksheet
rng.Copy
ActiveSheet.Range("A1").PasteSpecial xlValues
ActiveSheet.Range("A1").PasteSpecial xlFormats
ActiveWorkbook.SendMail Subject:="Myfiles",
"
ActiveWorkbook.Close SaveChanges:=False


End Sub

--
Regards,
Tom Ogilvy


"Josh Johansen" wrote:

Also, I have moved the pivot table down 8 cells in order to put some
extensive calculations on the top so that I could lock them and
wherever the
user was on the pivot table they could see these calculations, is there
any
way to include them on the copy, paste and email? Thanks again.

"Tom Ogilvy" wrote:

Recipient should be plural: Recipients

also, you seem to have a typo in the Workbooks.Add Line. The
constant is

Workbooks.Add Template:=xlWBATWorksheet

not

Workbooks.Add Template:=zlWBATWorksheet

--
Regards,
Tom Ogilvy


"Josh Johansen" wrote:

Here is what I copied:

Private Sub CommandButton1_Click()
Set rng = ActiveSheet.PivotTables(1).TableRange2
Workbooks.Add Template:=zlWBATWorksheet
ActiveSheet.Range("A1").Select
rng.Copy
Activesheeet.PasteSpecial xlValues
ActiveSheet.PasteSpecial xlFormats
ActiveWorkbook.SendMail Subject:="Scheduling",
"
ActiveWorkbook.Close SaveChanges:=False
End Sub

When I attempt to use the button I am getting. "Compile error,
named
argument not found." with the recipient portion highlighted. I
have to admit
I am not at all familiar with Visual Basic, what am I doing wrong?
Thanks
again.

"Tom Ogilvy" wrote:

ActiveSheet.PivotTables(1).TableRange2.Copy

will copy the data in the pivot Table including pagefields. If
you don't
want the pagefields, then use TableRange1

I might use something like:

set rng = ActiveSheet.PivotTables(1).TableRange2
workbooks.Add Template:=xlWBATWorksheet
Activesheet.Range("A1").Select
rng.copy
Activesheet.PasteSpecial xlValues
Activesheet.PasteSpecial xlFormats
ActiveWorkbook.Sendmail Subject:="Myfiles",
"
Activeworkbook.close SaveChanges:=False





--
Regards,
Tom Ogilvy


"Josh Johansen" wrote:

I guess the thing I am unsure how to do is write a program that
will select
the data currently displayed in the pivot table, copy that
data, paste it to
a new mail message all with a programmed button. Because
various users will
be using the pivot table, the size could be extremely
different, I will read
through the link you left me and hopefully find some useful
information,
thank you so much.

"Tom Ogilvy" wrote:

Take a look at Ron de Bruin's site:

http://www.rondebruin.nl/sendmail.htm

this has extensive information on mailing data from Excel.
Your description
isn't very definitive, so you will have to decide what is
appropriate.

--
Regards,
Tom Ogilvy


"Josh Johansen" wrote:

I would like to program a button that will copy a pivot
table, and then paste
it into a new email message. This will prevent the users
from being confused
with all the steps this would take manually. I am not too
familiar with how
to do this, any ideas? Thanks.