How to copy and paste based on quantity? Closed

Question

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


Configuration: Windows 7 / Safari 535.2

venkat1926 · Answer

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

How to copy and paste based on quantity?

1 response

Similar discussions

Subscribe To Our Newsletter!