vba copy and paste number of times based off cell value

PatrickWil

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

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,140
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,140
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

jarti

New Member
Joined
Jan 8, 2020
Messages
6
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,140
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
Can you please start a thread of your own for this question. Thanks
 

PatrickWil

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,140
Office Version
365
Platform
Windows
Just add this line
Rich (BB code):
 Next r
   Sheets("Sheet2").Cells.ClearContents
   Sheets("sheet2").Range("B2").Resize(nr).Value = Nary
End Sub
 

PatrickWil

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,140
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,914
Messages
5,471,482
Members
406,765
Latest member
FLCL

This Week's Hot Topics

Top