Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application activation and pasting
All,
I am trying to create a macro to select all data in a current workbook, copy it to the clipboard, create a new workbook and paste the data into that workbook. This is a sample of the code I am using: Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Workbooks.Add Workbooks(2).Activate Range("A1").Select ActiveSheet.Paste This code results in a new workbook being generated, the focus going to the new workbook, but the paste command is pasted overtop of my original spreadsheet... Any help would be appriciated. Thank you, Sam Barnhouse |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application activation and pasting
Hi Samuel
If you only want to copy the cells from the first sheet in the workbook you can use this Sub test() Dim wb1 As Workbook Dim wb2 As Workbook Set wb1 = ThisWorkbook Set wb2 = Workbooks.Add(1) wb1.Sheets(1).Cells.Copy wb2.Sheets(1).Cells(1) End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Samuel Barnhouse" wrote in message news:Q0h4e.179$KI6.60@okepread07... All, I am trying to create a macro to select all data in a current workbook, copy it to the clipboard, create a new workbook and paste the data into that workbook. This is a sample of the code I am using: Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Workbooks.Add Workbooks(2).Activate Range("A1").Select ActiveSheet.Paste This code results in a new workbook being generated, the focus going to the new workbook, but the paste command is pasted overtop of my original spreadsheet... Any help would be appriciated. Thank you, Sam Barnhouse |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application activation and pasting
"Samuel Barnhouse" wrote in message
news:Q0h4e.179$KI6.60@okepread07... I am trying to create a macro to select all data in a current workbook, copy it to the clipboard, create a new workbook and paste the data into that workbook. This is a sample of the code I am using: Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Workbooks.Add Workbooks(2).Activate Range("A1").Select ActiveSheet.Paste This code results in a new workbook being generated, the focus going to the new workbook, but the paste command is pasted overtop of my original spreadsheet... Any help would be appriciated. Hi Sam, Here's one way to do it: Sub PasteToNewWorkbook() Dim wkbDestination As Workbook Dim wksActive As Worksheet Set wksActive = ActiveSheet Set wkbDestination = Workbooks.Add(xlWorksheet) wksActive.Range(wksActive.Range("A1"), _ wksActive.UsedRange.SpecialCells(xlLastCell)).Copy _ Destination:=wkbDestination.Worksheets(1).Range("A 1") End Sub -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application activation and pasting
Both of these examples worked great.
I have figured out a problem, however. I am pulling via Oracle a ton of data and then sorting and orgranizing the data. This macro will be used on a scheduler to run once per month to pull the data and save the file out to a network drive (All of that is complete). I realized, however that I need all of the sheets in the workbook, not just the first sheet. So basically I need to save the entire workbook out, except with NO macros in the workbook... Again Thanks!! Sam Barnhouse "Samuel Barnhouse" wrote in message news:Q0h4e.179$KI6.60@okepread07... All, I am trying to create a macro to select all data in a current workbook, copy it to the clipboard, create a new workbook and paste the data into that workbook. This is a sample of the code I am using: Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Workbooks.Add Workbooks(2).Activate Range("A1").Select ActiveSheet.Paste This code results in a new workbook being generated, the focus going to the new workbook, but the paste command is pasted overtop of my original spreadsheet... Any help would be appriciated. Thank you, Sam Barnhouse |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application activation and pasting
Hi Samual
Like I posted you private if you want it easy then install my sendmail add-in and send a copy of the workbook to yourself with no macros (option in the Add-in) http://www.rondebruin.nl/mail/add-in.htm See Chip's site for code to remove the code http://www.cpearson.com/excel/vbe.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Samuel Barnhouse" wrote in message news:dPh4e.184$KI6.111@okepread07... Both of these examples worked great. I have figured out a problem, however. I am pulling via Oracle a ton of data and then sorting and orgranizing the data. This macro will be used on a scheduler to run once per month to pull the data and save the file out to a network drive (All of that is complete). I realized, however that I need all of the sheets in the workbook, not just the first sheet. So basically I need to save the entire workbook out, except with NO macros in the workbook... Again Thanks!! Sam Barnhouse "Samuel Barnhouse" wrote in message news:Q0h4e.179$KI6.60@okepread07... All, I am trying to create a macro to select all data in a current workbook, copy it to the clipboard, create a new workbook and paste the data into that workbook. This is a sample of the code I am using: Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Workbooks.Add Workbooks(2).Activate Range("A1").Select ActiveSheet.Paste This code results in a new workbook being generated, the focus going to the new workbook, but the paste command is pasted overtop of my original spreadsheet... Any help would be appriciated. Thank you, Sam Barnhouse |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
activation key | Excel Discussion (Misc queries) | |||
Cell formatting when pasting from a non-Office application | Excel Discussion (Misc queries) | |||
Activation Request | Excel Discussion (Misc queries) | |||
Sheet activation | Excel Discussion (Misc queries) | |||
application.volatile activation | Excel Programming |