Copy via code from a dynamic range using offeset

Hi out there,

Can someone help me with the syntax to refer to a dynamic range via
code?

I am trying to use activesheet with the following parameters:

- Start copy at cell CI6 (including CI6)
- Go down rows via value in cell CI2
- Past values to cell G6


TIA,

Greg

Range("CI6").Resize(Range("CI2"),1).Copy Destination:=Range("G6")

--
Regards,
Tom Ogilvy

"Greg" <[email protected]> wrote in message
news:[email protected]...
> Hi out there,
>
> Can someone help me with the syntax to refer to a dynamic range via
> code?
>
> I am trying to use activesheet with the following parameters:
>
> - Start copy at cell CI6 (including CI6)
> - Go down rows via value in cell CI2
> - Past values to cell G6
>
>
> TIA,
>
> Greg
>

Thanks Tom,

When I use your code as follows:

ThisWorkbook.ActiveSheet.Range("CI6").Resize(Range("CI2=AD"), 1).Copy
Destination:=3DRange("G6")


I get a "Method Range Of Object _Global Failed.

do I have a typo somewhere?

Greg

Tom,

Thanks for the quick reply and help. Disregard my e-mail about it not
working.

For some reason when I copied it out of Google Groups and into the VB
editor it did not come over right. I did not notice this till after
posting the 2nd question. I found the typo error and fixed and it is
working great!!

Greg

Sorry - me again.

I noticed that it is pasting cell contents versus values. How would I
keep the code in the simple one line and have it past values?

I guess I could split it to a copy and then a pastvalues line, but I
like it in one command.

Thanks again.

Pastespecial is a separate command.

Range("CI6").Resize(Range("CI2"),1).Copy
Range("G6").Pastespecial xlvalues

you could use

Range("G6").Resize(Range("CI2"),1).Value =
Range("CI6").Resize(Range("CI2"),1).Value

which is one line.

--
Regards,
Tom Ogilvy

"Greg" <[email protected]> wrote in message
news:[email protected]...
> Sorry - me again.
>
> I noticed that it is pasting cell contents versus values. How would I
> keep the code in the simple one line and have it past values?
>
> I guess I could split it to a copy and then a pastvalues line, but I
> like it in one command.
>
> Thanks again.
>