How do I reference a range in a macro that will change

[lizI3]

Hi,
Iam having trouble referencing a range in a macro that will copy and paste. If I want to copy b5 formula to C5:S5 (in excel) but later in the macro now want to copy B15 to C15:S15 how do you reference the row number so that it will pick your currect row?

Any suggests would be welcome

thanks
Liz

 

[Zathras]

Here is one way to do what you want:

Option Explicit

Sub Macro2()
Dim nRow As Long
  nRow = ActiveCell.Row
  CopyCells nRow
End Sub

Sub CopyCells(RowNumber As Long)
  Cells(RowNumber, 2).Copy _
     Range(Cells(RowNumber, 3), Cells(RowNumber, 19))
  Application.CutCopyMode = False
End Sub

Note that if more than one cell is selected, the "ActiveCell" is the upper left-hand cell in the range.