VBA Creating list based on variables - insert different values predefined times

Question

I want to create column that will be filled by city names repeated "x" times.

Data taken from another sheet (Sheet1, column A (text), B (text) and F (formula)):

• London Q 3
• Paris R 2

Want to have (Sheet2, column A (text), B (text) and C (Number)):

• London Q 1
• London Q 2
• London Q 3
• Paris R 1
• Paris R 2

I know it is quite easy but I'm new in VBA :/ I've found code like below (from description it should do what I want), but - this loop never ends and xls crushes so I'm not able to see if it is doing what I want or not.

    Sub RunMe()
Dim CopyX, x As Integer
CopyX = Sheets("Sheet2").Range("F1")
Sheets("Sheet1").Select
Range("A1").Copy

Do
    x = x + 1
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Loop Until x = CopyX
Application.CutCopyMode = False
End Sub

Answer 1

This code - loops through each item in sheet1 - repeats the city name however many times are specified in col F - puts a 1 alongside the first entry - completes the series in successive cells in steps of 1 until the col F value is reached.

You may have to adjust sheet names.

Sub x()

Dim r As Long, ws2 As Worksheet

Set ws2 = Sheets("Sheet2")

With Sheets("Sheet1")
    For r = 1 To .Range("A" & Rows.Count).End(xlUp).Row
        ws2.Range("A" & Rows.Count).End(xlUp)(2).Resize(.Cells(r, 6).Value).Value = .Cells(r, 1).Resize(, 2).Value
        ws2.Range("B" & Rows.Count).End(xlUp)(2).Value = 1
        ws2.Range("B" & Rows.Count).End(xlUp).DataSeries Step:=1, Rowcol:=xlColumns, Type:=xlLinear, Stop:=.Cells(r, 6).Value
    Next r
End With

End Sub