Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Melbourne, Australia
    Thanked 0 Times in 0 Posts

    xllastcell and copying (Excel 2000)

    Hello Everyone,

    I have setup a macro so that I will move to the last cell and then copy that column of data and the column before it.
    Then I move to another column and paste.

    My problem: The columns will change, and when I run the marco it always copies the same 2 columns.
    Is there any code that will make it dynamic and select the last row, even when it changes?

    I have copied and pasted the code below:



    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: xllastcell and copying (Excel 2000)

    You probably recorded this macro. By default, a macro will be recorded with absolute references, that is, if you select a cell, the absolute cell address is recorded. The Recording toolbar has a button that lets you switch to relative references, i.e. if you select a cell, the offset from the current cell is recorded. If you use relative references, the macro will probably do what you will.

    But in many cases, the recorded code is less than ideal. If I understand your description correctly, the following code will do the same, without all that selecting going on:<pre>Dim rng As Range
    Set rng = ActiveSheet.Cells.SpecialCells(xlLastCell)
    rng.Offset(0, -1).Range("A1:B1").Copy Destination:=rng.Offset(0, 2)</pre>

    The variable rng is set to the "last cell" (see note), then the range consisting of that cell and the one to the left of it is copied, and pasted in the same instruction to the cell two columns to the right.

    Note: during the editing of a worksheet, the "last cell" is not always kept up-to-date. Search for "last cell" in this forum for more information and alternative ways to find it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts