Copy and Paste in the first empty available line.

[Etienne]

Hello All,

I am starting to write a VBa code in order to copy 4 cells on the same line
in another sheet in order to keep the history. Right now, the present code
below just overwrite the previous data everytime I run it. I would like to be
able to have Excel copy on the next available empty line below the previous
one. How should I change my code?

Sub CopyandPaste()
Dim CopyRange As Range
Set CopyRange = Range("B5:D5")
CopyRange.Copy Destination:=Worksheets("Hist").Range("B7:D7")
End Sub

Thanks for your help.

Etienne

[PCLIVE]

This isn't test...so give it a shot.

Sub CopyandPaste()
Dim CopyRange As Range
Set CopyRange = Range("B5:D5")
CopyRange.Copy Destination:=Worksheets("Hist") _
.Range("B" & (Range("B65536").End(xlUp).Row) & ":D" &
(Range("B65536").End(xlUp).Row))
End Sub


"Etienne" <Etienne@discussions.microsoft.com> wrote in message
news:278AE426-08A8-4096-B62C-8EE6F5AFD3B3@microsoft.com...
> Hello All,
>
> I am starting to write a VBa code in order to copy 4 cells on the same
> line
> in another sheet in order to keep the history. Right now, the present code
> below just overwrite the previous data everytime I run it. I would like to
> be
> able to have Excel copy on the next available empty line below the
> previous
> one. How should I change my code?
>
> Sub CopyandPaste()
> Dim CopyRange As Range
> Set CopyRange = Range("B5:D5")
> CopyRange.Copy Destination:=Worksheets("Hist").Range("B7:D7")
> End Sub
>
> Thanks for your help.
>
> Etienne

[Etienne]

I get an error message of compiling at &":D"

Do you know why?

Thanks,

"PCLIVE" wrote:

> This isn't test...so give it a shot.
>
> Sub CopyandPaste()
> Dim CopyRange As Range
> Set CopyRange = Range("B5:D5")
> CopyRange.Copy Destination:=Worksheets("Hist") _
> .Range("B" & (Range("B65536").End(xlUp).Row) & ":D" &
> (Range("B65536").End(xlUp).Row))
> End Sub
>
>
> "Etienne" <Etienne@discussions.microsoft.com> wrote in message
> news:278AE426-08A8-4096-B62C-8EE6F5AFD3B3@microsoft.com...
> > Hello All,
> >
> > I am starting to write a VBa code in order to copy 4 cells on the same
> > line
> > in another sheet in order to keep the history. Right now, the present code
> > below just overwrite the previous data everytime I run it. I would like to
> > be
> > able to have Excel copy on the next available empty line below the
> > previous
> > one. How should I change my code?
> >
> > Sub CopyandPaste()
> > Dim CopyRange As Range
> > Set CopyRange = Range("B5:D5")
> > CopyRange.Copy Destination:=Worksheets("Hist").Range("B7:D7")
> > End Sub
> >
> > Thanks for your help.
> >
> > Etienne
>
>
>

[Etienne]

Sorry, I made an error typing.

Yet, the macro works but keeps on overwriting the previous data. Excel does
not copy on the next available line.

Etienne

"PCLIVE" wrote:

> This isn't test...so give it a shot.
>
> Sub CopyandPaste()
> Dim CopyRange As Range
> Set CopyRange = Range("B5:D5")
> CopyRange.Copy Destination:=Worksheets("Hist") _
> .Range("B" & (Range("B65536").End(xlUp).Row) & ":D" &
> (Range("B65536").End(xlUp).Row))
> End Sub
>
>
> "Etienne" <Etienne@discussions.microsoft.com> wrote in message
> news:278AE426-08A8-4096-B62C-8EE6F5AFD3B3@microsoft.com...
> > Hello All,
> >
> > I am starting to write a VBa code in order to copy 4 cells on the same
> > line
> > in another sheet in order to keep the history. Right now, the present code
> > below just overwrite the previous data everytime I run it. I would like to
> > be
> > able to have Excel copy on the next available empty line below the
> > previous
> > one. How should I change my code?
> >
> > Sub CopyandPaste()
> > Dim CopyRange As Range
> > Set CopyRange = Range("B5:D5")
> > CopyRange.Copy Destination:=Worksheets("Hist").Range("B7:D7")
> > End Sub
> >
> > Thanks for your help.
> >
> > Etienne
>
>
>

[Ron de Bruin]

See
http://www.rondebruin.nl/copy1.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Etienne" <Etienne@discussions.microsoft.com> wrote in message news:542A1894-765B-4DA9-9E8C-713E87E78E3C@microsoft.com...
> Sorry, I made an error typing.
>
> Yet, the macro works but keeps on overwriting the previous data. Excel does
> not copy on the next available line.
>
> Etienne
>
> "PCLIVE" wrote:
>
>> This isn't test...so give it a shot.
>>
>> Sub CopyandPaste()
>> Dim CopyRange As Range
>> Set CopyRange = Range("B5:D5")
>> CopyRange.Copy Destination:=Worksheets("Hist") _
>> .Range("B" & (Range("B65536").End(xlUp).Row) & ":D" &
>> (Range("B65536").End(xlUp).Row))
>> End Sub
>>
>>
>> "Etienne" <Etienne@discussions.microsoft.com> wrote in message
>> news:278AE426-08A8-4096-B62C-8EE6F5AFD3B3@microsoft.com...
>> > Hello All,
>> >
>> > I am starting to write a VBa code in order to copy 4 cells on the same
>> > line
>> > in another sheet in order to keep the history. Right now, the present code
>> > below just overwrite the previous data everytime I run it. I would like to
>> > be
>> > able to have Excel copy on the next available empty line below the
>> > previous
>> > one. How should I change my code?
>> >
>> > Sub CopyandPaste()
>> > Dim CopyRange As Range
>> > Set CopyRange = Range("B5:D5")
>> > CopyRange.Copy Destination:=Worksheets("Hist").Range("B7:D7")
>> > End Sub
>> >
>> > Thanks for your help.
>> >
>> > Etienne
>>
>>
>>

[Etienne]

Perfect.

Thanks a lot for your help.

Etienne

"Ron de Bruin" wrote:

> See
> http://www.rondebruin.nl/copy1.htm
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Etienne" <Etienne@discussions.microsoft.com> wrote in message news:542A1894-765B-4DA9-9E8C-713E87E78E3C@microsoft.com...
> > Sorry, I made an error typing.
> >
> > Yet, the macro works but keeps on overwriting the previous data. Excel does
> > not copy on the next available line.
> >
> > Etienne
> >
> > "PCLIVE" wrote:
> >
> >> This isn't test...so give it a shot.
> >>
> >> Sub CopyandPaste()
> >> Dim CopyRange As Range
> >> Set CopyRange = Range("B5:D5")
> >> CopyRange.Copy Destination:=Worksheets("Hist") _
> >> .Range("B" & (Range("B65536").End(xlUp).Row) & ":D" &
> >> (Range("B65536").End(xlUp).Row))
> >> End Sub
> >>
> >>
> >> "Etienne" <Etienne@discussions.microsoft.com> wrote in message
> >> news:278AE426-08A8-4096-B62C-8EE6F5AFD3B3@microsoft.com...
> >> > Hello All,
> >> >
> >> > I am starting to write a VBa code in order to copy 4 cells on the same
> >> > line
> >> > in another sheet in order to keep the history. Right now, the present code
> >> > below just overwrite the previous data everytime I run it. I would like to
> >> > be
> >> > able to have Excel copy on the next available empty line below the
> >> > previous
> >> > one. How should I change my code?
> >> >
> >> > Sub CopyandPaste()
> >> > Dim CopyRange As Range
> >> > Set CopyRange = Range("B5:D5")
> >> > CopyRange.Copy Destination:=Worksheets("Hist").Range("B7:D7")
> >> > End Sub
> >> >
> >> > Thanks for your help.
> >> >
> >> > Etienne
> >>
> >>
> >>
>
>
>