Hello All

I want to select a worksheet from a workbook, open a new workbook and
paste the worksheet into it. Almost all will be 'paste formulas' but
I want to be able, if possible, to paste some values instead in
certain cells. VBA beginner, so I've tried to do it with the record
method. Resulting macro copies the worksheet to the clipboard ok, but
then pastes it back into the same worksheet instead of a new workbook.

Try this

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

HTH
Mark.

From the Macro Recorder:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/27/2008 by James Ravenswood
'

'
Sheets("Sheet3").Copy
ChDir "C:\Documents and Settings\Owner\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Owner\Desktop\Book4.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
End Sub

--
Gary''s Student - gsnu2007j


" wrote:

Hello All

I want to select a worksheet from a workbook, open a new workbook and
paste the worksheet into it. Almost all will be 'paste formulas' but
I want to be able, if possible, to paste some values instead in
certain cells. VBA beginner, so I've tried to do it with the record
method. Resulting macro copies the worksheet to the clipboard ok, but
then pastes it back into the same worksheet instead of a new workbook.

On Jul 27, 12:35*pm, Gary''s Student
wrote:
From the Macro Recorder:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/27/2008 by James Ravenswood
'

'
* * Sheets("Sheet3").Copy
* * ChDir "C:\Documents and Settings\Owner\Desktop"
* * ActiveWorkbook.SaveAs Filename:= _
* * * * "C:\Documents and Settings\Owner\Desktop\Book4.xls",
FileFormat:=xlNormal, _
* * * * Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
* * * * CreateBackup:=False
* * ActiveWorkbook.Close
End Sub

--
Gary''s Student - gsnu2007j



" wrote:
Hello All

I want to select a worksheet from a workbook, open a new workbook and
paste the worksheet into it. *Almost all will be 'paste formulas' but
I want to be able, if possible, to paste some values instead in
certain cells. *VBA beginner, so I've tried to do it with the record
method. *Resulting macro copies the worksheet to the clipboard ok, but
then pastes it back into the same worksheet instead of a new workbook.- Hide quoted text -

- Show quoted text -

Thanks, Nood & Gary. How can I adjust Macro2() to not save the new
workbook (the one pasted to) but just keep it open?