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).Worksheets("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

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).Worksheets("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
>
>

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).Worksheets("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
>
>

On 9 Apr., 15:48, Vergel Adriano
<VergelAdri...@discussions.microsoft.com> 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

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.

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
> <VergelAdri...@discussions.microsoft.com> 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
>
>

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

On 9 Apr., 17:04, Vergel Adriano
<VergelAdri...@discussions.microsoft.com> 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
> > <VergelAdri...@discussions.microsoft.com> 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