| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
| Copy range using offset range value | Excel Programming | |||
| How to apply OFFSET as the range in a basic 'Copy' process... | Excel Discussion (Misc queries) | |||
| Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming | |||
| Select and Copy Range using Offset | Excel Programming | |||
![Kjeldc[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
