Copy/Paste multiple times based on cell value

[Cooksey19]

Hi I had found some code from an old post that is close to what I need, but need some help altering it to make it just a bit better. Currently, i have a 2 column/1 row array selected then click a button to run my macro. Then I will enter into the box how many times I want to copy/paste this data (which will vary, the number it varies by is in a cell to the left of the data selected). So lets say my selection is K6:L6, the number of duplicated I would like is J6. The selection can be anywhere from row 6 through (currently) 27, but there is no reason it couldn't be longer, that's just what I needed so far. So typically I can loop through this macro many times to complete the data i'm processing. Select row 6, duplicate x number times, then row 7, duplicate Y number times...etc until I get through all the rows populated. So i'm not sure if getting through the whole table can be automated, but even just removing the need to have the input box would be helpful.

The pasting is setup where I would like it. So it would go into an array (A5:D104), so that's why I go to row 105, then go up to the next available row. Any help I could get would be greatly appreciated.

Sub TransferData()
Selection.Copy
Dim i As Integer
Dim inputNumber As Integer
inputNumber = InputBox("How many times do you want to copy/paste data?")
For i = 1 To inputNumber
    Range ("A105").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1,0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Next i
End Sub

 

[mumps]

Will the number of times to duplicate always be in column J? Will the first row that you want to start duplicating always be row 6? Do you want the duplicated data to always be pasted starting in row 106 or simply to paste in the first available row?

 

[Cooksey19]

Will the number of times to duplicate always be in column J? Will the first row that you want to start duplicating always be row 6? Do you want the duplicated data to always be pasted starting in row 106 or simply to paste in the first available row?

Yes the duplicated number will always be in J, next to the data in K & L that I want to copy/duplicate. So the data will be duplicated into an array (first available cell is A6). Everything duplicated after this point will be in the next available row. So if at first I duplicated twice, then my next copy/duplication would be in row 8.

I attached a picture of my file, hopefully that helps.

 

[mumps]

So if at first I duplicated twice, then my next copy/duplication would be in row 8.

Are you saying that you want to paste the data directly below the data to duplicate? I still don't quite follow. It's hard to work with a picture. Could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. It would also help if you could manually create a second sheet that displays your desired results based on the data and attach a screen shot of the results as well. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. (de-sensitized if necessary).

 

[Cooksey19]

Are you saying that you want to paste the data directly below the data to duplicate? I still don't quite follow. It's hard to work with a picture. Could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. It would also help if you could manually create a second sheet that displays your desired results based on the data and attach a screen shot of the results as well. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. (de-sensitized if necessary).

Yes I will definitely have to de-sensitize my file. I'll do that then upload and post a link.

 

[mumps]

You don't need to include all the rows of data. A dozen or so should be enough to give me an idea of how the data is organized

 

[Cooksey19]

Non-Contiguous CopyPaste V3.xlsm

Shared with Dropbox

www.dropbox.com

Here you go. Let me know of any other questions I can help to clarify. File name was for a previous problem i was working on, so not applicable.

 

[Fluff]

How about

Sub Cooksey()
   Dim Cl As Range
   
   For Each Cl In Range("J6", Range("J" & Rows.Count).End(xlUp))
      Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1 * Cl.Value, 2).Value = Cl.Offset(, 1).Resize(, 2).Value
   Next Cl
End Sub

 

[mumps]

It looks like Fluff has offered you a solution.

 

[Cooksey19]

How about

Sub Cooksey()
   Dim Cl As Range
  
   For Each Cl In Range("J6", Range("J" & Rows.Count).End(xlUp))
      Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1 * Cl.Value, 2).Value = Cl.Offset(, 1).Resize(, 2).Value
   Next Cl
End Sub

So this worked! But i'm also still getting an error. In the array i'm moving things to, I got exactly what I want. But it still comes up: Run-time Error '1004' Application-defined or object defined error. This occurs in the line starting with Range("A" &...

So not sure what that is about. Also can you please provide a note on each line as to what it is doing. Usually when code is so short, I can tell what it's doing. But I think using resize, which I've never done, is throwing me off.