Copy range with offset

  • Thread starter Thread starter Kjeldc
  • Start date Start date
K

Kjeldc

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
 
Hello,

you can do it like

..Offset(-1, 13).range("A1:G1")Select
Selection.Copy
.Offset(0, 13).Select
ActiveSheet.Paste
 
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")
 
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 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
 
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
 
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:
 
Resizes the specified range. Returns a Range object that represents the
resized range.

expression.Resize(RowSize, ColumnSize)

Try
Range("A1").Resize(5, 2).Select
 
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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto Fill Columns A and B with varying ranges 6
Select Case Code does not run... 10
Clean this select case code up a bit. 2
Help with Index formula 2
Run-time error 1004 paste method of worksheet class failed 3
Macro to fill to next field with data 4
Error using offset 1
Worksheet_beforedoubleclick 2

Back
Top