Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste ranges with offset Excel 2000
Hello,
I¨m working with copyinf and pasting cell in differents sheets, and ait works well but when I try to do the same with range I have problems. I use this code. ------------------------------- Range("B2").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = Workbooks("ReportRP.xls").Sheets("PS").Range("D13: D15") ActiveCell.Offset(0, -1) = _ Workbooks("ReportRP.xls").Sheets("PS").Range("I6:I 10") ____________________________________ the same code refering only to one cell works well, I think the problem is the Offset, can I change it??. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste ranges with offset Excel 2000
The problem with these lines is that you are trying to set one cell (the
active cell) equal to 3 cells (D13:D15). ActiveCell.Value = Workbooks("ReportRP.xls").Sheets("PS").Range("D13: D15") ActiveCell.Offset(0, -1) = _ Workbooks("ReportRP.xls").Sheets("PS").Range("I6:I 10") If you wanted to put the 3 cells at the bottom of the list, then you could use ActiveCell.RANGE("A1:A3").Value = Workbooks("ReportRP.xls").Sheets("PS").Range("D13: D15").VALUE This would place the cells D13:D15 into the active cell and the 2 cells below it (the A1:A3 bit is relative to the activeCell) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste ranges with offset Excel 2000
I can't see any problems with the code (apart from testing for blank at the
start of the loop in case B2 is empty), but this is a more efficient way of doing it With Range("B2").End(xlDown).Offset(1, 0) .Value = Workbooks("ReportRP.xls").Sheets("PS").Range("D13: D15") .Offset(0, -1) = _ Workbooks("ReportRP.xls").Sheets("PS").Range("I6:I 10") End With -- HTH Bob Phillips "carloshernandezy" wrote in message ups.com... Hello, I¨m working with copyinf and pasting cell in differents sheets, and ait works well but when I try to do the same with range I have problems. I use this code. ------------------------------- Range("B2").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = Workbooks("ReportRP.xls").Sheets("PS").Range("D13: D15") ActiveCell.Offset(0, -1) = _ Workbooks("ReportRP.xls").Sheets("PS").Range("I6:I 10") ____________________________________ the same code refering only to one cell works well, I think the problem is the Offset, can I change it??. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste ranges with offset Excel 2000
Oops, missed the treble cell bit (I commented it out in my tests).
That as well, the approach is more efficient. -- HTH Bob Phillips "Bob Phillips" wrote in message ... I can't see any problems with the code (apart from testing for blank at the start of the loop in case B2 is empty), but this is a more efficient way of doing it With Range("B2").End(xlDown).Offset(1, 0) .Value = Workbooks("ReportRP.xls").Sheets("PS").Range("D13: D15") .Offset(0, -1) = _ Workbooks("ReportRP.xls").Sheets("PS").Range("I6:I 10") End With -- HTH Bob Phillips "carloshernandezy" wrote in message ups.com... Hello, I¨m working with copyinf and pasting cell in differents sheets, and ait works well but when I try to do the same with range I have problems. I use this code. ------------------------------- Range("B2").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = Workbooks("ReportRP.xls").Sheets("PS").Range("D13: D15") ActiveCell.Offset(0, -1) = _ Workbooks("ReportRP.xls").Sheets("PS").Range("I6:I 10") ____________________________________ the same code refering only to one cell works well, I think the problem is the Offset, can I change it??. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges and Offset | Excel Worksheet Functions | |||
Using Offset with named ranges | Excel Worksheet Functions | |||
Ranges using offset | Excel Programming | |||
Different passwords to separate ranges in excel 2000 | Excel Programming | |||
How do I copy and paste ranges from VB 6.0 in an Excel spreadsheet? | Excel Programming |