Pasting data to a dynamic range using VBA

[CMDMA03]

I am trying to paste a single cell value to a range that will vary according to row counts in two different columns. I'm pasting in column B so I want to use .Range("B" & BRowCount) as my starting value. I then want to paste the value down column B until I reach the last used row in column C (i.e., .Range("B" & CRowCount)). None of the paste range code that I am trying seems to work. Does anyone have suggesstions?

With CS
        BRowCount = CS.Range("B65536").End(xlUp).Row
        BRowCount = BRowCount + 1
        .Range("B" & BRowCount).PasteSpecial Paste:=xlPasteValues
        .Range("B" & BRowCount).Copy
        CRowCount = CS.Range("C65536").End(xlUp).Row
        '---->Paste Code Inserted Here
End With

 

[cstimart]

Does this fit your needs (untested)

CS.Select
     BRowCount = Cells(Rows.Count, 2).End(xlUp).Row
     CRowCount = Cells(Rows.Count, 3).End(xlUp).Row
     [C1].Offset(CRowCount).Value = [B1].Offset(BRowCount).Value

 

[CMDMA03]

Didn't work. I've also tried the code below with no luck. I've used code that has a dynamic end point before (e.g., .Range("B2:B" & BRowCount)), but have not ever needed something to have a dynamic begin and end point.

.Range("B" & BRowCount:"B" & CCowCount).PasteSpecial Paste:=xlPasteValues

rngStart = .Range("B" & BRowCount)
rngEnd = .Range("B" & CRowCount)
.Range(rngStart:rngEnd).PasteSpecial Paste:=xlPasteValues

 

[cstimart]

OK, I need to get some clarification..

You're pasting a value in the first open cell in Column B and filling the rest of the column until Column C is empty?

If so...does this do it?

Dim rng As Range
BRowCount = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
CRowCount = Cells(Rows.Count, 3).End(xlUp).Row
    For Each rng In Range("B" & BRowCount & ":B" & CRowCount)
        rng.PasteSpecial xlPasteValues
    Next rng

 

[CMDMA03]

Worked like a charm. Thanks!