Previous Topic Next Topic

How to copy and paste based on quantity?

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
Hello,

This is first time I ever need to ask question on Excel. I got a problem which is very time consuming and it would be helpful if there is a faster way of doing this.

I need to turn this

Column1 Column2 Column3
1064 France 3
1065 Britain 2
1067 Britain 5

into this

Column1 Column2 Column3
1064 France 1
1064 France 1
1064 France 1
1065 Britain 1
1065 Britain 1
1067 Britain 1
1067 Britain 1
1067 Britain 1
1067 Britain 1
1067 Britain 1

Thanks for any help!

Cheers

Jo


1 reply

Answer 1 / 1
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
try this macro (note: row1 contain headers and data only from row no. 2) 

Sub test()
Dim rc As Range, c As Range, dest As Range, j As Long
With Worksheets("sheet1")
Set rc = Range(.Range("c2"), .Range("C2").End(xlDown))
For Each c In rc
j = c.Value
c.EntireRow.Copy
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Range(dest, dest.Offset(2, 0)).PasteSpecial
End With
Next c
End With
With Worksheets("sheet2")
Range(.Range("C2"), .Range("C2").End(xlDown)).FormulaArray = 1
End With
End Sub
Ask a question