vba copy and paste number of times based off cell value

[PatrickWil]

I cant figure a vba code to do the following:

--------sheet1-----------
-----(A)---------(B)------
-----Qty.----Description
1]----3---------sofa-----
2]----1---------chair----
3]----5---------table----

based on (sheet1 column A's value)......copy and paste (sheet1 column B's value) onto sheet2 that # of times. the list could potentially be hundreds of items long.

----------sheet2---------
-------(A)---------(B)-----
----Barcode--Description
1]-----------------sofa---
2]-----------------sofa---
3]-----------------sofa---
4]-----------------chair--
5]-----------------table--
6]-----------------table--
7]-----------------table--
8]-----------------table--
9]-----------------table--

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub PatrickWil()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, rr As Long, nr As Long
   
   With Sheets("Sheet1")
      Ary = .Range("A1").CurrentRegion.Value2
      ReDim Nary(1 To Application.Sum(.Range("A:A")), 1 To 1)
   End With
   For r = 2 To UBound(Ary)
      For rr = 1 To Ary(r, 1)
         nr = nr + 1
         Nary(nr, 1) = Ary(r, 2)
      Next rr
   Next r
   Sheets("sheet2").Range("B2").Resize(nr).Value = Nary
End Sub

 

[PatrickWil]

absolutely the answer amazing thanks!!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[jarti]

This is so close to what I've been searching for! Is there an easy way to copy over only the last row of cells in columns A:D (sheet 1) the number of times as specified in that row, col E? As opposed to all the rows in the first sheet and copying over only col B values?

 

[Fluff]

Hi & welcome to MrExcel.
Can you please start a thread of your own for this question. Thanks

 

[PatrickWil]

a co worker of mine liked this and asked for a code that IF you alter sheet1 say delete a line
then run the command code again can it delete data on sheet two and re enter the data. I know its possible but I definitely don't know

 

[Fluff]

Just add this line

 Next r
   Sheets("Sheet2").Cells.ClearContents
   Sheets("sheet2").Range("B2").Resize(nr).Value = Nary
End Sub

 

[PatrickWil]

perfect thank you it is the code I was trying to use I just wasn't placing it right. thank you so much for your help.

 

[Fluff]

Glad to help & thanks for the feedback