MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Range Error


Posted by Sam on July 26, 2001 9:23 AM

I'm having trouble with a certain macro that uses the range command. What I need done is to copy a cell in sheet 2 and paste it in the same column x rows down. The x is the same number of rows as sheet 1 uses. Here is my current code if anyone could give suggestion on how to improve this code or has a better code all together that would help out a lot.

Sub Test()
Dim EndRow As Integer
Sheets(1).Select
Range("a1").Select
EndRow = Selection.End(xlDown).Count
Sheets(2).Select
Range("a3").Select
Selection.Copy
Range(ActiveCell, EndRow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A3").Range("A1").Select

End Sub

Thanks,
Sam


Posted by Barrie Davidson on July 26, 2001 11:58 AM

Hi Sam, try changing:
Range(ActiveCell, EndRow).Select

to:
ActiveCell.Offset(EndRow, 0).Select

I think this will do the trick for you.

Regards,
Barrie

Posted by Sam on July 26, 2001 12:32 PM

Once Again, Thanks For The Great Help Barrie!