Macro- Relative reference

[tb81]

I am trying to record a macro that will copy a certain cell, go to the last
entry in a column and paste the value. I am using Excel 2007. I have been
able to copy and past using the macro but the relative reference function has
not worked. The macro throws and error and visual basic identifies the
second to last line of code as the issue. Here is the code. Does anyone
know how to get around this issue?

Range("D1").Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-9

 

[RyanH]

You are getting the error because you are setting the CutCopyMode to False
which clears the clipboard, thus you have nothing to paste. Here is some
code that should work for you.


Sub CopyAndPaste()

Dim lngLastRow As Long

' finds last row with data in Col.D
lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row

' copies D1 and paste in last row
Range("D1").Copy Destination:=Range("D" & lngLastRow + 1)

End Sub

 

[Don Guillett]

One liner

mc = "e"
Cells(1, mc).Copy Cells(Cells(Rows.Count, mc).End(xlUp).Row + 1, mc)