| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
Hello,
you can do it like ..Offset(-1, 13).range("A1:G1")Select Selection.Copy .Offset(0, 13).Select ActiveSheet.Paste -- Kevin Smith  )"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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 )> > > "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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 )> > > > > > "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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 )> > > > > > > > > "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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 )"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 )> > > > > > > > > "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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 )> > > > > > > > > > > > "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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 )> > > > > > > > > > > > > > > "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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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 )> > > > > > > > > > > > > > > > > > "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 |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Find, Copy offset to offset on other sheet, Run-time 1004. | Finny | Microsoft Excel Programming | 10 | 7th Dec 2006 11:46 PM |
| Copy range using offset range value | =?Utf-8?B?Y2Fyb2xpbmU=?= | Microsoft Excel Programming | 2 | 16th Feb 2006 02:51 PM |
| How to apply OFFSET as the range in a basic 'Copy' process... | =?Utf-8?B?Y2Rhdmlkc29u?= | Microsoft Excel Misc | 4 | 8th Nov 2005 08:43 PM |
| Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | erazmus@actrix.co.nz | Microsoft Excel Programming | 1 | 22nd Aug 2005 03:31 AM |
| Select and Copy Range using Offset | jondorv | Microsoft Excel Programming | 1 | 16th May 2004 05:23 PM |
