Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Problem
Replace tthis statement
from: ws.Range("B3").Copy xlSheet.Range("A1") ' <<< This is highlighted to: xlSheet.Range("A1") = ws.Range("B3") "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Problem
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... -- HTH... Jim Thomlinson "Joel" wrote: Replace tthis statement from: ws.Range("B3").Copy xlSheet.Range("A1") ' <<< This is highlighted to: xlSheet.Range("A1") = ws.Range("B3") "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Problem
On May 10, 1:30 pm, "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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste problem | Excel Discussion (Misc queries) | |||
Copy/ Paste Problem | Setting up and Configuration of Excel | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy/Paste Problem | Excel Programming | |||
Copy/Paste Problem | Excel Programming |