xllastcell and copying (Excel 2000)

[kerrie]

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:

Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Range("H12:I12").Select


Range("I12").Activate
Selection.Copy
ActiveCell.SpecialCells(xlLastCell).Select
Range("K12").Select
ActiveSheet.Paste

Thanks Kindly,

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

[HansV]

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.