ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy range with offset (https://www.excelbanter.com/excel-programming/436954-copy-range-offset.html)

Kjeldc[_3_]
Copy range with offset
 
I know how to copy a cell like this:

..Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


..Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Kevin Smith[_2_]
Copy range with offset
 
Hello,

you can do it like

..Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

--
Kevin Smith :o)


"Kjeldc" wrote:

I know how to copy a cell like this:

.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


.Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Jacob Skaria
Copy range with offset
 
You dont need to select a cell to copy. The below will do

..Offset(-1, 13).Copy .Offset(0, 13)

Similarly... copy to another sheet

Range("A1:C20").Copy Sheets("Sheet2").Range("C1")

--
Jacob


"Kevin Smith" wrote:

Hello,

you can do it like

.Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

--
Kevin Smith :o)


"Kjeldc" wrote:

I know how to copy a cell like this:

.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


.Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Kjeldc[_3_]
Copy range with offset
 
Thanks, but I need to copy 9 cells, and now I do it like this:


.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

.Offset(-1, 14).Select
Selection.Copy
.Offset(0, 14).Select
ActiveSheet.Paste

.Offset(-1, 15).Select
Selection.Copy
.Offset(0, 15).Select
ActiveSheet.Paste

Is there a better way?

My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You dont need to select a cell to copy. The below will do

.Offset(-1, 13).Copy .Offset(0, 13)

Similarly... copy to another sheet

Range("A1:C20").Copy Sheets("Sheet2").Range("C1")

--
Jacob


"Kevin Smith" wrote:

Hello,

you can do it like

.Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

--
Kevin Smith :o)


"Kjeldc" wrote:

I know how to copy a cell like this:

.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


.Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Jacob Skaria
Copy range with offset
 
You havent posted your full code....Are you currently in Active cell. Try the
below

..Offset(-1, 13).Resize(1, 9).Copy .Offset(0, 13)


Jacob


"Kjeldc" wrote:

Thanks, but I need to copy 9 cells, and now I do it like this:


.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

.Offset(-1, 14).Select
Selection.Copy
.Offset(0, 14).Select
ActiveSheet.Paste

.Offset(-1, 15).Select
Selection.Copy
.Offset(0, 15).Select
ActiveSheet.Paste

Is there a better way?

My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You dont need to select a cell to copy. The below will do

.Offset(-1, 13).Copy .Offset(0, 13)

Similarly... copy to another sheet

Range("A1:C20").Copy Sheets("Sheet2").Range("C1")

--
Jacob


"Kevin Smith" wrote:

Hello,

you can do it like

.Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

--
Kevin Smith :o)


"Kjeldc" wrote:

I know how to copy a cell like this:

.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


.Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Kevin Smith[_2_]
Copy range with offset
 
Hello.

This will copy the cells that you require.
The range A1:G1 starts from the activecell not the actual range A1:G1

..Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste
--
Kevin Smith :o)


"Kjeldc" wrote:

Thanks, but I need to copy 9 cells, and now I do it like this:


.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

.Offset(-1, 14).Select
Selection.Copy
.Offset(0, 14).Select
ActiveSheet.Paste

.Offset(-1, 15).Select
Selection.Copy
.Offset(0, 15).Select
ActiveSheet.Paste

Is there a better way?

My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You dont need to select a cell to copy. The below will do

.Offset(-1, 13).Copy .Offset(0, 13)

Similarly... copy to another sheet

Range("A1:C20").Copy Sheets("Sheet2").Range("C1")

--
Jacob


"Kevin Smith" wrote:

Hello,

you can do it like

.Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

--
Kevin Smith :o)


"Kjeldc" wrote:

I know how to copy a cell like this:

.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


.Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Kjeldc[_3_]
Copy range with offset
 
Thanks Jacob. I dont understand the "Resize" part, but it seems to do it :-))
--
My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You havent posted your full code....Are you currently in Active cell. Try the
below

.Offset(-1, 13).Resize(1, 9).Copy .Offset(0, 13)


Jacob


"Kjeldc" wrote:

Thanks, but I need to copy 9 cells, and now I do it like this:


.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

.Offset(-1, 14).Select
Selection.Copy
.Offset(0, 14).Select
ActiveSheet.Paste

.Offset(-1, 15).Select
Selection.Copy
.Offset(0, 15).Select
ActiveSheet.Paste

Is there a better way?

My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You dont need to select a cell to copy. The below will do

.Offset(-1, 13).Copy .Offset(0, 13)

Similarly... copy to another sheet

Range("A1:C20").Copy Sheets("Sheet2").Range("C1")

--
Jacob


"Kevin Smith" wrote:

Hello,

you can do it like

.Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

--
Kevin Smith :o)


"Kjeldc" wrote:

I know how to copy a cell like this:

.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


.Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Jacob Skaria
Copy range with offset
 
Resizes the specified range. Returns a Range object that represents the
resized range.

expression.Resize(RowSize, ColumnSize)

Try
Range("A1").Resize(5, 2).Select

--
Jacob


"Kjeldc" wrote:

Thanks Jacob. I dont understand the "Resize" part, but it seems to do it :-))
--
My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You havent posted your full code....Are you currently in Active cell. Try the
below

.Offset(-1, 13).Resize(1, 9).Copy .Offset(0, 13)


Jacob


"Kjeldc" wrote:

Thanks, but I need to copy 9 cells, and now I do it like this:


.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

.Offset(-1, 14).Select
Selection.Copy
.Offset(0, 14).Select
ActiveSheet.Paste

.Offset(-1, 15).Select
Selection.Copy
.Offset(0, 15).Select
ActiveSheet.Paste

Is there a better way?

My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You dont need to select a cell to copy. The below will do

.Offset(-1, 13).Copy .Offset(0, 13)

Similarly... copy to another sheet

Range("A1:C20").Copy Sheets("Sheet2").Range("C1")

--
Jacob


"Kevin Smith" wrote:

Hello,

you can do it like

.Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

--
Kevin Smith :o)


"Kjeldc" wrote:

I know how to copy a cell like this:

.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


.Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld

Kjeldc[_3_]
Copy range with offset
 
I love it. Saves a lot of code writing :-)
--
My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

Resizes the specified range. Returns a Range object that represents the
resized range.

expression.Resize(RowSize, ColumnSize)

Try
Range("A1").Resize(5, 2).Select

--
Jacob


"Kjeldc" wrote:

Thanks Jacob. I dont understand the "Resize" part, but it seems to do it :-))
--
My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You havent posted your full code....Are you currently in Active cell. Try the
below

.Offset(-1, 13).Resize(1, 9).Copy .Offset(0, 13)


Jacob


"Kjeldc" wrote:

Thanks, but I need to copy 9 cells, and now I do it like this:


.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

.Offset(-1, 14).Select
Selection.Copy
.Offset(0, 14).Select
ActiveSheet.Paste

.Offset(-1, 15).Select
Selection.Copy
.Offset(0, 15).Select
ActiveSheet.Paste

Is there a better way?

My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


"Jacob Skaria" skrev:

You dont need to select a cell to copy. The below will do

.Offset(-1, 13).Copy .Offset(0, 13)

Similarly... copy to another sheet

Range("A1:C20").Copy Sheets("Sheet2").Range("C1")

--
Jacob


"Kevin Smith" wrote:

Hello,

you can do it like

.Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste

--
Kevin Smith :o)


"Kjeldc" wrote:

I know how to copy a cell like this:

.Offset(-1, 13).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Past-

But how can I copy a range like this


.Offset(-1, 13-21).Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste


My programming is self-taught and
my teacher was not very experienced. :-)

cheers,
Kjeld


All times are GMT +1. The time now is 03:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com