Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste to another Workbook, Error 1004
Hello everybody,
I am fairly new to VBA and I have come to a point where I need some help. I am trying to copy from one excel file to another. I could do that using various Windows.Activate, Wokrsheet.activate lines and i works that way. But I would like to do it in a more elegant way. I am using the following code: Do Application.Workbooks(filename).Worksheets("test2" ).Range(Cells(counter, 1), Cells(counter, 6)).Copy Destination:=Application.Workbooks(filename).Works heets("test2").Range(Cells(counter, 8), Cells(counter, 13)) counter = counter + (60 * values_per_sec) targetcounter = targetcounter + 1 Loop While counter <= nr_of_rec filename has been defined by the user, nr_of_rec and values_per_sec are read out of the file "filename". Now, while the code above is working fine, I do not want to copy and paste in the same workbook (as the code above does), but into another, thus I write: Application.Workbooks(filename).Worksheets("test2" ).Range(Cells(counter, 1), Cells(counter, 6)).Copy Destination:=Application.Workbooks("Auswerter.xls" ).Worksheets("Data").Range(Cells(targetcounter, 1), Cells(targetcounter, 6)) "Auswerter.xls" is the target file and "Data" the targer worksheet. But this returns a runtime error 1004 "Application-defined or Object- defined error". It would be great if someone could explain to me, why I get this error and how I could get rid of it. Thanks a lot! Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste to another Workbook, Error 1004
I think you will need to have the workbook open that you are wanting to paste
into or you will need to know the path to the workbook you are wanting to paste into hope this will help "Steve" wrote: Hello everybody, I am fairly new to VBA and I have come to a point where I need some help. I am trying to copy from one excel file to another. I could do that using various Windows.Activate, Wokrsheet.activate lines and i works that way. But I would like to do it in a more elegant way. I am using the following code: Do Application.Workbooks(filename).Worksheets("test2" ).Range(Cells(counter, 1), Cells(counter, 6)).Copy Destination:=Application.Workbooks(filename).Works heets("test2").Range(Cells(counter, 8), Cells(counter, 13)) counter = counter + (60 * values_per_sec) targetcounter = targetcounter + 1 Loop While counter <= nr_of_rec filename has been defined by the user, nr_of_rec and values_per_sec are read out of the file "filename". Now, while the code above is working fine, I do not want to copy and paste in the same workbook (as the code above does), but into another, thus I write: Application.Workbooks(filename).Worksheets("test2" ).Range(Cells(counter, 1), Cells(counter, 6)).Copy Destination:=Application.Workbooks("Auswerter.xls" ).Worksheets("Data").Range(Cells(targetcounter, 1), Cells(targetcounter, 6)) "Auswerter.xls" is the target file and "Data" the targer worksheet. But this returns a runtime error 1004 "Application-defined or Object- defined error". It would be great if someone could explain to me, why I get this error and how I could get rid of it. Thanks a lot! Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste to another Workbook, Error 1004
You need to make sure that the target workbook is open, then try it this way:
Dim rng As Range With Application.Workbooks(Filename).Worksheets("test2" ) Set rng = .Range(.Cells(counter, 1), .Cells(counter, 6)) rng.Copy Destination:=Application.Workbooks("Auswerter.xls" ).Worksheets("Data").Range(rng.Address) End With -- Hope that helps. Vergel Adriano "Steve" wrote: Hello everybody, I am fairly new to VBA and I have come to a point where I need some help. I am trying to copy from one excel file to another. I could do that using various Windows.Activate, Wokrsheet.activate lines and i works that way. But I would like to do it in a more elegant way. I am using the following code: Do Application.Workbooks(filename).Worksheets("test2" ).Range(Cells(counter, 1), Cells(counter, 6)).Copy Destination:=Application.Workbooks(filename).Works heets("test2").Range(Cells(counter, 8), Cells(counter, 13)) counter = counter + (60 * values_per_sec) targetcounter = targetcounter + 1 Loop While counter <= nr_of_rec filename has been defined by the user, nr_of_rec and values_per_sec are read out of the file "filename". Now, while the code above is working fine, I do not want to copy and paste in the same workbook (as the code above does), but into another, thus I write: Application.Workbooks(filename).Worksheets("test2" ).Range(Cells(counter, 1), Cells(counter, 6)).Copy Destination:=Application.Workbooks("Auswerter.xls" ).Worksheets("Data").Range(Cells(targetcounter, 1), Cells(targetcounter, 6)) "Auswerter.xls" is the target file and "Data" the targer worksheet. But this returns a runtime error 1004 "Application-defined or Object- defined error". It would be great if someone could explain to me, why I get this error and how I could get rid of it. Thanks a lot! Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste to another Workbook, Error 1004
On 9 Apr., 15:48, Vergel Adriano
wrote: You need to make sure that the target workbook is open, then try it this way: Dim rng As Range With Application.Workbooks(Filename).Worksheets("test2" ) Set rng = .Range(.Cells(counter, 1), .Cells(counter, 6)) rng.Copy Destination:=Application.Workbooks("Auswerter.xls" ).Worksheets("Data").Range(rng.Address) End With -- Hope that helps. Vergel Adriano Thanks for the help. Your approach works fine, it finally copies it to the target file. But I do not really get anywhere, because I paste the data to the same adress as I took them from. My intention is to copy every 20th or so line into a different file (which I can do now, thanks to your help). But in that file ("Auswerter.xls") the lines are supposed to be directly underneath one another. So, what I did was change the rng.Adress part to an different range: Dim counter As Long counter = 21 Dim targetcounter As Integer targetcounter = 1 Dim rng As Range Dim trng As Range 'this is the target range Do Set trng = Application.Workbooks("Auswerter.xls").Worksheets( "Data").Range(Cells(targetcounter, 1), Cells(targetcounter, 6)) With Application.Workbooks(filename).Worksheets("test2" ) Set rng = .Range(.Cells(counter, 1), .Cells(counter, 6)) rng.Copy Destination:=trng End With counter = counter + (60 * values_per_sec) 'this counter advances faster and thus many lines in the original file are skipped, as is intended. targetcounter = targetcounter + 1 ' the targetcounter is advancing one step at a time, thus going down one line at a time in the targetsheet. Loop While counter <= nr_of_rec And this again generates the same error 1004 as before. I am really stuck at the moment. How can it be, that the program can copy and paste to the same adress in different files but not to different adresses in different files? help would be greatly appreciated! Thanks! Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste to another Workbook, Error 1004
Ah, and by the way, the target workbook is the one calling the makro.
And the makro opens the original file, from where I want to copy. Thus both workbooks are open. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste to another Workbook, Error 1004
Steve,
The only line where I think your current code would error outis on this line: Set trng = Application.Workbooks("Auswerter.xls").Worksheets( "Data").Range(Cells(targetcounter, 1), Cells(targetcounter, 6)) You will get the 1004 error if the line executes and the "Data" worksheet is not the active sheet. I believe the call to Cells(targetcounter, x) will return a range in the current active sheet in the current active workbook. So, try that line like this and see if it helps: With Application.Workbooks("Auswerter.xls").Worksheets( "Data") Set trng = .Range(.Cells(targetcounter, 1), .Cells(targetcounter, 6)) End With -- Hope that helps. Vergel Adriano "Steve" wrote: On 9 Apr., 15:48, Vergel Adriano wrote: You need to make sure that the target workbook is open, then try it this way: Dim rng As Range With Application.Workbooks(Filename).Worksheets("test2" ) Set rng = .Range(.Cells(counter, 1), .Cells(counter, 6)) rng.Copy Destination:=Application.Workbooks("Auswerter.xls" ).Worksheets("Data").Range(rng.Address) End With -- Hope that helps. Vergel Adriano Thanks for the help. Your approach works fine, it finally copies it to the target file. But I do not really get anywhere, because I paste the data to the same adress as I took them from. My intention is to copy every 20th or so line into a different file (which I can do now, thanks to your help). But in that file ("Auswerter.xls") the lines are supposed to be directly underneath one another. So, what I did was change the rng.Adress part to an different range: Dim counter As Long counter = 21 Dim targetcounter As Integer targetcounter = 1 Dim rng As Range Dim trng As Range 'this is the target range Do Set trng = Application.Workbooks("Auswerter.xls").Worksheets( "Data").Range(Cells(targetcounter, 1), Cells(targetcounter, 6)) With Application.Workbooks(filename).Worksheets("test2" ) Set rng = .Range(.Cells(counter, 1), .Cells(counter, 6)) rng.Copy Destination:=trng End With counter = counter + (60 * values_per_sec) 'this counter advances faster and thus many lines in the original file are skipped, as is intended. targetcounter = targetcounter + 1 ' the targetcounter is advancing one step at a time, thus going down one line at a time in the targetsheet. Loop While counter <= nr_of_rec And this again generates the same error 1004 as before. I am really stuck at the moment. How can it be, that the program can copy and paste to the same adress in different files but not to different adresses in different files? help would be greatly appreciated! Thanks! Steve |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste to another Workbook, Error 1004
Ok, finally, it runs:
Do With Application.Workbooks("Auswerter.xls").Worksheets( "data") Set trng = .Range(.Cells(targetcounter, 1), .Cells(targetcounter, 6)) End With With Application.Workbooks(filename).Worksheets("test2" ) Set rng = .Range(.Cells(counter, 1), .Cells(counter, 6)) rng.Copy Destination:=trng End With Still, if anyone could explain to me, why this works and not the other way, it would be great. Thanks again! Steve |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste to another Workbook, Error 1004
On 9 Apr., 17:04, Vergel Adriano
wrote: Steve, The only line where I think your current code would error outis on this line: Set trng = Application.Workbooks("Auswerter.xls").Worksheets( "Data").Range(Cells(targetcounter, 1), Cells(targetcounter, 6)) You will get the 1004 error if the line executes and the "Data" worksheet is not the active sheet. I believe the call to Cells(targetcounter, x) will return a range in the current active sheet in the current active workbook. So, try that line like this and see if it helps: With Application.Workbooks("Auswerter.xls").Worksheets( "Data") Set trng = .Range(.Cells(targetcounter, 1), .Cells(targetcounter, 6)) End With -- Hope that helps. Vergel Adriano "Steve" wrote: On 9 Apr., 15:48, Vergel Adriano wrote: You need to make sure that the target workbook is open, then try it this way: Dim rng As Range With Application.Workbooks(Filename).Worksheets("test2" ) Set rng = .Range(.Cells(counter, 1), .Cells(counter, 6)) rng.Copy Destination:=Application.Workbooks("Auswerter.xls" ).Worksheets("Data").Range(rng.Address) End With -- Hope that helps. Vergel Adriano Thanks for the help. Your approach works fine, it finally copies it to the target file. But I do not really get anywhere, because I paste the data to the same adress as I took them from. My intention is to copy every 20th or so line into a different file (which I can do now, thanks to your help). But in that file ("Auswerter.xls") the lines are supposed to be directly underneath one another. So, what I did was change the rng.Adress part to an different range: Dim counter As Long counter = 21 Dim targetcounter As Integer targetcounter = 1 Dim rng As Range Dim trng As Range 'this is the target range Do Set trng = Application.Workbooks("Auswerter.xls").Worksheets( "Data").Range(Cells(targetcounter, 1), Cells(targetcounter, 6)) With Application.Workbooks(filename).Worksheets("test2" ) Set rng = .Range(.Cells(counter, 1), .Cells(counter, 6)) rng.Copy Destination:=trng End With counter = counter + (60 * values_per_sec) 'this counter advances faster and thus many lines in the original file are skipped, as is intended. targetcounter = targetcounter + 1 ' the targetcounter is advancing one step at a time, thus going down one line at a time in the targetsheet. Loop While counter <= nr_of_rec And this again generates the same error 1004 as before. I am really stuck at the moment. How can it be, that the program can copy and paste to the same adress in different files but not to different adresses in different files? help would be greatly appreciated! Thanks! Hey, thanks a lot! It worked! Greets Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004 Paste method of Worksheet class failed | Excel Programming | |||
Copy Paste not working from Add-in - Error 1004 | Excel Programming | |||
Error 1004 in search/copy | Excel Programming | |||
runtime error 1004 paste method of worksheet class failed | Excel Programming | |||
Run Time error 1004 Paste Method of Worksheet Class Failed | Excel Programming |