I am having trouble with "offset" commands in macros.Can someone help me with
code to do the following:
I wish to copy a range two cells to the right of a given cell (after
Selection.End(xlDown).Select) through to the end of the data to the right.
(anything from 1 to 50 columns)

Set rngAtEnd = ActiveCell.End(xlDown).End(xlToRight)
rngAtEnd.Copy
--
Jim Cone
Portland, Oregon USA


"Barry Lennox"
wrote in message
I am having trouble with "offset" commands in macros.
Can someone help me with code to do the following:
I wish to copy a range two cells to the right of a given cell (after
Selection.End(xlDown).Select) through to the end of the data to the right.
(anything from 1 to 50 columns)

Thanks Jim
A help but not quite there. Your code gives me the last cell to the right (a
code that I will be able to use and adapt at another time) But I actually
want to copy the whole range from two cells to the right through to the last
occupied cell to the right)

Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 2)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).Copy
(assumes row data is contiguous)
--
Jim Cone
Portland, Oregon USA



"Barry Lennox"
wrote in message
Thanks Jim
A help but not quite there. Your code gives me the last cell to the right (a
code that I will be able to use and adapt at another time) But I actually
want to copy the whole range from two cells to the right through to the last
occupied cell to the right)

Thanks Jim
It works well. Although I don't yet fully understand it. (I am slowly
learning as I go)
Can you now help me with a loop to clear the whole column (working from the
top up) so that it stops when nothing else is there. In the final operation I
will be transferring data to another sheet and printing between loops.
The code I have is below
Range("GdInvRng").End(xlDown).Select
Selection.Copy
Range("T161").Select
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste
Range("f164").Select
Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 2)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).Copy
Range("T161").Select
ActiveCell.Offset(2, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Range("F164").Select
Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 0)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).ClearContents
Selection.End(xlDown).Select
Range("F163:R163").Select
Application.CutCopyMode = False
Selection.ClearContents

Hi

I'm not sure which columns should be deleted, but I cleaned up your
code a bit, as you don't need to use select (it slow down code).

Range("GdInvRng").End(xlDown).Copy Destinatin:=Range("T161").Offset(2,
0)
Set rngAtStart = Range("f164").End(xlDown).Offset(0, 2)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).Copy
Range("T161").Offset(2, 1).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Set rngAtStart = Range("F164").End(xlDown).Offset(0, 0)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).ClearContents
Range("F163:R163").ClearContents
Application.CutCopyMode = False


Regards,
Per

On 24 Dec., 11:09, Barry Lennox
<BarryLen...@discussions.microsoft.com> wrote:
> Thanks Jim
> It works well. Although I don't yet fully understand it. (I am slowly
> learning as I go)
> Can you now help me with a loop to clear the whole column (working from the
> top up) so that it stops when nothing else is there. In the final operation I
> will be transferring data to another sheet and printing between loops.
> The code I have is below
> Range("GdInvRng").End(xlDown).Select
> *Selection.Copy
> * Range("T161").Select
> * ActiveCell.Offset(2, 0).Select
> *ActiveSheet.Paste
> *Range("f164").Select
> *Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 2)
> Set rngAtEnd = rngAtStart.End(xlToRight)
> Range(rngAtStart, rngAtEnd).Copy
> * * Range("T161").Select
> * *ActiveCell.Offset(2, 1).Select
> * * * Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
> * * * * False, Transpose:=True
> * * Range("F164").Select
> * * Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 0)
> Set rngAtEnd = rngAtStart.End(xlToRight)
> Range(rngAtStart, rngAtEnd).ClearContents
> Selection.End(xlDown).Select
> * * Range("F163:R163").Select
> * * Application.CutCopyMode = False
> * * Selection.ClearContents

Thanks Per
But there is a problem. I get a "compile error syntax error" message with
the following lines in red:
Range("T161").Offset(2, 1).Paste Special Paste:=xlPasteAll,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Where to from here

The line continuation character is missing from the end of the first line.
It should read... Paste:=xlPasteAll, _

The second and third lines can (if desired) be combined to read...
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Also, in case you missed it... "Destinatin" should be Destination and
..Offset(0, 0) can be removed - it does nothing.
--
Jim Cone
Portland, Oregon USA



"Barry Lennox"
wrote in message
Thanks Per
But there is a problem. I get a "compile error syntax error" message with
the following lines in red:
Range("T161").Offset(2, 1).Paste Special Paste:=xlPasteAll,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Where to from here

Thanks Guys
This works. Next question how do I put a loop in so that it goes through the
list and stops after the last (in this case first entry). At present I have
to rerun the code but it eventually leaves the remaining entry and then
starts on a table below the original one (in F173)

Hi Barry

Thanks for your reply.

What is the list range which you want to loop through. Where do you
want the output?

---
Per

On 26 Dec., 07:39, Barry Lennox
<BarryLen...@discussions.microsoft.com> wrote:
> Thanks Guys
> This works. Next question how do I put a loop in so that it goes through the
> list and stops after the last (in this case first entry). At present I have
> to rerun the code but it eventually leaves the remaining entry and then
> starts on a table below the original one (in F173)