Programing a button in excel

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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", Recipient:="(e-mail address removed)"
Activeworkbook.close SaveChanges:=False

 

[Guest]

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", Recipient:="(e-mail address removed)"
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.

 

[Guest]

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

 

[Guest]

You are right, i had two typos. Now what the problem is that i click the
command button, it takes me to a new sheet, and I receive the following:

Run-time error '1004':

PasteSpecial method of Worksheet class failed

I really wish I had more experience with visual basic so i really appreciate
your help, my boss walked in and i explained what I was trying to do and he
was very happy with the idea, anything to make it easier for the users I
guess, anyways I will keep trying to figure out what is wrong, but if you
have any ideas that would be great, thanks again.

 

[Guest]

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.

 

[Guest]

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", Recipients:="(e-mail address removed)"
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", Recipients:="(e-mail address removed)"
ActiveWorkbook.Close SaveChanges:=False


End Sub

 

[Guest]

All right, everything worked great, I have a problem with sizing when it
copies, but that should be easy to fix, my next question is: The email
automatically sends to whoever I put in the code, how do I set it up so that
it will open to the email and allow the user to select who they want it sent
to? Many users will use this different times but it will be sent to other
individuals. Thanks so much, this is working great!

 

[Guest]

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]

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

 

[Guest]

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.