Copy range with offset

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
 
K

Kevin Smith

Hello,

you can do it like

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

Jacob Skaria

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")
 
K

Kjeldc

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:
 
J

Jacob Skaria

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
 
K

Kevin Smith

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
 
K

Kjeldc

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:
 
J

Jacob Skaria

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

expression.Resize(RowSize, ColumnSize)

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

Kjeldc

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

VBA Coding Help for Beginner 0
Help merging two VBA codes 2
Select Case Code does not run... 10
End(xlDown) Issue 1
Clean this select case code up a bit. 2
Copy row info but not in normal column sequence 6
Conditional If/Else statement 2
Auto Fill Columns A and B with varying ranges 6

Top