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

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

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

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

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