Help with selecting a range of cells

[Mick]

This is just a small part of my macro, I do hope the eagle eyed amongst you
can spot my mistake, all I want to do is select a range of cells in a row
and copy them?

I don't want to specify the range of cells by naming the rows/columns as I
need to use the code elsewhere in my macro i.e ("B5:J5")

Sub CopyRowAndAddAsNewRow()
Dim StartCell As String
Dim EndCell As String
Range("A1").Select
ActiveCell.Offset(4, 1).Select
StartCell = ActiveCell
EndCell = ActiveCell.Offset(0, 8)
Range("StartCell:EndCell").Select
Selection.Copy
End Sub

 

[Norman Jones]

Hi Mick,

Sub CopyRowAndAddAsNewRow()
Dim StartCell As Range
Dim EndCell As Range
Range("A1").Select
ActiveCell.Offset(4, 1).Select
Set StartCell = ActiveCell
Set EndCell = ActiveCell.Offset(0, 8)
Range(StartCell, EndCell).Select
Selection.Copy

End Sub

or, avoiding unnecessary selections and simplifying a little

Sub CopyRowAndAddAsNewRow2()
Dim StartCell As Range
Dim EndCell As Range

Set StartCell = Range("A1").Offset(4, 1)
Set EndCell = StartCell.Offset(0, 8)
Range(StartCell, EndCell).Copy

End Sub

 

[Chip]

Let me know if you need variable names, otherwise...quick and easy..

Range("A1").Select
ActiveCell.Offset(4, 1).Select
Range(ActiveCell, ActiveCell.Offset(0, 8)).Select
Selection.Copy

 

[Guest]

You can replace all of your code with this one line...

Range(Range("A1").Offset(4, 1), Range("A1").Offset(4, 9)).Copy

HTH

 

[Mick]

Thank you all for the suggestions, it no longer ceases to amaze me how may
different ways there are to do the same thing. Your help is very much
appreciated.

Regards
Mick