Give this code a whirl if you wish...

Sub Test()
Dim csFILENAME As String
Dim wsh As Object
Dim xlApp As Application
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim ws As Excel.Worksheet

Set wsh = CreateObject("wscript.shell")
csFILENAME = wsh.SpecialFolders.Item("Desktop")

Set xlBook = Workbooks.Add(Template:="Workbook")
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Visible = True

Set ws = ActiveSheet

ws.Range("B3").Copy xlSheet.Range("A1") '<<< This is highlighted

xlBook.SaveAs (csFILENAME & "\" & "Test " & _
Format(Now(), "mm-dd-yy") & ".xls")
xlBook.Close

Set xlApp = Nothing

End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

The problem you are having is that you are creating a new instacne of XL and
trying to copy and paste between instances. That won't work. You need to
create the new book in the current instance of XL. If you look in your task
manager you will probably see a bunch of instances of XL. One instance has no
ability to talk to any other instance...

Before we go modifying a buch of code though why are you creating the new
instance of XL???
--
HTH...

Jim Thomlinson


"Dan R." wrote:

I'm trying to open a new WB, copy & paste values from the ActiveSheet
to the WB, then close & save the WB on the desktop. When I run this I
get a run-time error '1004': "Copy method of Range class failed" and
my copy/paste line is highlighted.


Sub Test()
Dim csFILENAME As String
Dim wsh As Object
Dim xlApp As Application
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim ws As Excel.Worksheet

Set wsh = CreateObject("wscript.shell")
csFILENAME = wsh.SpecialFolders.Item("Desktop")

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add( Template:="Workbook")
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Visible = True

Set ws = ActiveSheet

ws.Range("B3").Copy xlSheet.Range("A1") <<< This is highlighted

xlBook.SaveAs (csFILENAME & "\" & "Test " & _
Format(Now(), "mm-dd-yy") & ".xls")
xlBook.Close

Set xlApp = Nothing

End Sub


Thanks,
-- Dan