Naming a cell

[fahimip]

I need to name a cell so that i can copy a range using the named cell as a reference.
the named cell will change every time the loop is completed.

heres what ive got so far:


Sub test()

Sheets("HD").Select
Range("G3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
mycell = ActiveCell
Range("E3:" & mycell).Select
Selection.copy
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.Paste

End Sub

Any ideas?

 

[Richard Schollar]

Hi

This is one way you could reproduce the code you have:

Sub Test()
With Sheets("HD")
   Set myCell = .Range("G3").End(xlDown).Offset(-1,0)
   .Range("E3:" & myCell.Address).Copy Destination:=.Range("A65536").End(xlUp).Offset(1,0)
End With
End Sub

Best regards

Richard