Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. "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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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! "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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programing a button in excel
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel programing | Excel Discussion (Misc queries) | |||
Excel VB Programing | Excel Programming | |||
this is about excel programing | Excel Programming | |||
Programing Outlook using Excel | Excel Programming | |||
help with excel programing | Excel Programming |