Do Loop with variable output

Hi,

I have written the code below to basically populate column C within a sheet called Source. It takes the contents of the column BH in the same sheet and appends a hypen and a number corresponding to the row.
For example: AO145-090909-1 where AO145-090909 is the contents of C, the - is appended and 1 is the row that it is present within.

The problem that I am having is around:

ActiveCell.FormulaR1C1 = "=(RC[-42]&""-""&RowCount)"

By adding the variable RowCount into this part of the code it fails. But I need to add the row count into the cell.

Sub test()

Dim Counter As Variant
Dim RowCount As Variant

    Counter = 2
    RowCount = 1
    
    Do While Counter < 101

        Sheets(Source).Select
        Range("BH" & Counter).Select
        ActiveCell.FormulaR1C1 = "=(RC[-42]&""-""&RowCount)"
        Range("BH" & Counter).Select
        Selection.Copy
        Range("S" & Counter).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Range("BH" & Counter).Delete
        Counter = Counter + 1
        RowCount = RowCount + 1

    Loop
    
End Sub

Thanks for any help, its greatly receieved.

tubbsy

Thats great. Thanks royUK.

Is it possible to adapt this code to reference mulitiple sheets?

For instance, if I wanted to populate the cell A1 with concataneted data from two separate cells in two separate sheets. I had written the following code to do this:

Sub test()

Dim Counter as Integer

Counter = 2

    Do While Counter < 101

        Sheets("Raw_Data").Select
        Range("AF" & Counter).Select
        ActiveCell.FormulaR1C1 = "=TRIM(RC[-23]&"", ""&RC[-22])"
        Range("AF" & Counter).Select
        Selection.Copy
        Sheets("Base_Data_Template").Select
        Range("E" & Counter).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Sheets("Raw_Data").Select
        Range("AF" & Counter).Delete
        Counter = Counter + 1

    Loop

End Sub

Thanks again