[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

Excel VB to copy and paste a number of rows based upon a cell number

I have a sheet called Production label that pulls information from other cells on other sheets.  I need to copy and paste the information in these cells onto another sheet multiple times based on a value in a cell.  Production label sheet has Columns setup starting at A1 and going to J1 titled as Company, Job, Mark, Finish, Height, Width, Model, Batch, Production, number. Each row pulls the information from other sheets.

I need a script that will copy the entire row, except the number cell, to another sheet multiple times.  The multiple being the value in the number cell.

I know nothing about VB.

Thanks in advance.
0
jwright77
Asked:
jwright77
  • 3
  • 3
1 Solution
 
Jon von der HeydenCommented:
Is it A1:I1 that you want to copy, or is it the data beneath it?
And when you paste it multiple times, whre should each iteration paste to?  The next empty row in the sheet?
0
 
jwright77Author Commented:
The data beneath it.

The next emtpy row in the sheet would be great.

Thanks.
0
 
Jon von der HeydenCommented:
This should be a good place to start.  Please take not of the comments in the code.
Sub Copy_Again()
 
Dim shtSrc As Worksheet, shtDest As Worksheet, lCount As Long, lRow As Long, lIt As Long
 
Set shtSrc = Sheets("Sheet1") '// change to refer to your source sheet name here //
Set shtDest = Sheets("Sheet2") '// change to refer to your destination sheet here //
 
lCount = shtSrc.Range("J2")
If lCount < 2 Then Exit Sub
 
Application.ScreenUpdating = False
 
For lIt = 1 To lCount
    shtSrc.Range("A2:I2").Copy
    shtDest.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Next lIt
 
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jwright77Author Commented:
That works great! Thanks.  The only thing is that on the first worksheet there is going to be multiple rows of information that I need to do this on.  Is it possible to put this in another loop and perform the same process on each row?

0
 
Jon von der HeydenCommented:
Do you mean that each row in your source sheet will have a number beside it in column J, and that number will determine how many times that row should be copied to the desitnation sheet?  If so please try the code below.
Sub Copy_Again()
 
Dim shtSrc As Worksheet, shtDest As Worksheet, lCount As Long, lRow As Long, lIt1 As Long, lIt2 As Long, lRowCount As Long
 
Set shtSrc = Sheets("Sheet1") '// change to refer to your source sheet name here //
Set shtDest = Sheets("Sheet2") '// change to refer to your destination sheet here //
 
lRowCount = shtSrc.Range("A" & Rows.Count).End(xlUp).Row
 
Application.ScreenUpdating = False
 
For lIt1 = 2 To lRowCount
    For lIt2 = 1 To shtSrc.Range("J" & lIt1).Value
        shtSrc.Range("A" & lIt1).Resize(1, 9).Copy
            shtDest.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
    Next lIt2
Next lIt1
 
End Sub

Open in new window

0
 
jwright77Author Commented:
Thanks JONvdHeyden:.  Just what I needed!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now