vba copy and paste number of times based off cell value

PatrickWil

New Member
Joined
Jan 3, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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--
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi & welcome to MrExcel.
How about
VBA Code:
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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?
 
Upvote 0
Hi & welcome to MrExcel.
Can you please start a thread of your own for this question. Thanks
 
Upvote 0
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
 
Upvote 0
Just add this line
Rich (BB code):
 Next r
   Sheets("Sheet2").Cells.ClearContents
   Sheets("sheet2").Range("B2").Resize(nr).Value = Nary
End Sub
 
Upvote 0
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.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,708
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top