Advice on copying and pasting single cell value to cells on different sheet - number of which specified

[LPepito]

Hi everyone,
I am a new to VBA and could really do with some help. I have a table that specifies text in one column and a number in the neighboring column. I'm trying to build a macro that will copy the text on a different sheet the same number of times specified in that neighboring column starting from the first empty cell on the new sheet. I've tried a bunch of different things, but can't seem to get it to work.

At the moment, my code is:

Sub CopyValuesAcross

Dim NextFree As String
Dim DC As Worksheet
Dim MR As Worksheet
Dim PRange As Rnage
Dim SelectionValue As Range

Set DC As Sheets("Sheet1")
Set MR As Sheets("Sheet2")

DC.Select

Set PRange = DC.Range("B5:B15")
Set SelectionValue = DC.Range("C5:C15")

For each cell in PRange

If Not IsEmpty(cell) Then
Selection.Copy

MR.Select

NextFree = Range("A2:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row

Range("A" & NextFree).Select

Selection.PasteSpecial xlPasteValues

End If

Next

End Sub

 

[LPepito]

Welcome to the MrExcel forum!

Try:

Sub CopyValuesAcross()
Dim PRange As Range, MR As Range, c As Range

    Set PRange = Worksheets("Sheet1").Range("B5:B15")
    Set MR = Worksheets("Sheet2").Range("A2")
   
    For Each c In PRange
        If c <> "" Then
            Set MR = MR.Offset(Rows.Count - MR.Row).End(xlUp).Offset(1)
            MR.Resize(c.Offset(, 1)).Value = c.Value
        End If
    Next c
   
End Sub

Thanks Eric - that's been really helpful! Despite some bumbling on my part, fixed by Alex, the code was really awesome - cheers!