Copy/Paste multiple times based on cell value

Cooksey19

New Member
Joined
Sep 30, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,736
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

New Member
Joined
Sep 30, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Picture.jpg
    Picture.jpg
    144.5 KB · Views: 6

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,736
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

New Member
Joined
Sep 30, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2012
Messages
9,736
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

New Member
Joined
Sep 30, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,736
It looks like Fluff has offered you a solution. :)
 

Cooksey19

New Member
Joined
Sep 30, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
How about
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,441
Members
410,684
Latest member
LakTik
Top