Copy/Paste Problem

[Dan R.]

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

 

[Joel]

Replace tthis statement
from:
ws.Range("B3").Copy xlSheet.Range("A1") ' <<< This is highlighted
to:
xlSheet.Range("A1") = ws.Range("B3")

 

[Jim Thomlinson]

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???

 

[Jim Thomlinson]

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

 

[Jim Thomlinson]

Your code assumes that Dan only wants the value to come through. Not the
formula if it is one, or any of the formatting. Your code will work but there
are limitations to what you can do between instances of XL...

 

[okrob]

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

Or this worked for me...

Sub Test()
Dim csFILENAME As String
Dim wsh As Object
Dim ws As Excel.Worksheet
Set wsh = CreateObject("wscript.shell")
csFILENAME = wsh.SpecialFolders.Item("Desktop")
Set ws = ActiveSheet
ws.Range("B3").Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs (csFILENAME & "\" & "Test " & _
Format(Now(), "mm-dd-yy") & ".xls")
ActiveWorkbook.Close
End Sub

 

[Dan R.]

I thought (wrongly) if I created a new instance of Excel it might
work, but yes I did have to kill it a couple times.

Both of your solutions work, but the copy/paste is a bit more
complicated than ws.Range("B3").Copy xlSheet.Range("A1"), I just used
that to keep it simple. So it looks like I'll just have to add a new
tab to the ActiveWorkbook first, then copy that to a new wb.

Thanks for all the help guys.

-- Dan