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

 

[Eric W]

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

 

[LPepito]

Thanks so much - really appreciate your help! Just having a look at that and I am seeming to get caught on the MR.Resize line. Maybe I've copied it wrong? Sorry!

 

[Alex Blakenburg]

Can you provide some visibility of your data (include the Row & Column references). Eric has had to guess at the layout and I think your columns might be the other way around. The code is copying B using the number of times from C and I am thinking it might be the other way around.

 

[LPepito]

Hi Alex - sorry about that, I should have done that in the first place. The first screenshot is the data and the second is what I'd like it to look like - hopefully that makes sense?

 

[Alex Blakenburg]

(include the Row & Column references)

A high proportion of the screenshots we get miss this vital step.

 

[LPepito]

Oh wow - that was really silly of me, thanks for your patience - proper shots attached.

 

[Alex Blakenburg]

Based on that and setting up equivalent data. The code works fine for me.
Is that definitely the first error message you get ?
Do you have any even macros running ?

 

[LPepito]

Super weird, it's working perfectly now - I have no idea what happened, maybe I was running something else in the background. I'm sorry, but thank you to both you and Eric. Really helped me out, cheers!

 

[Alex Blakenburg]

You're welcome. Glad Eric's code did the trick.