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

 

[Fluff]

Did you get the error on your test file, or your real file?

 

[Cooksey19]

Did you get the error on your test file, or your real file?

On my real file, but that particular sheet, there is absolutely nothing different. Every cell is the same. What are some possibilities? In the meantime, i'll also put it into my test file to see if it happens there too

Edit: It indeed does not mess up my test file.

 

[Fluff]

To get the error you reported I can only think it's trying to paste the data below the last row in the sheet.
If you go to cell A1048576 & hit Ctrl & the up arrow, where do you end up?

 

[Cooksey19]

To get the error you reported I can only think it's trying to paste the data below the last row in the sheet.
If you go to cell A1048576 & hit Ctrl & the up arrow, where do you end up?

If I have data populated, it's the last row with data in it. If it's empty it goes to row 5, where the header label is.

 

[Fluff]

Ok, that's fine, do you have any cells in col J with 0, or any very large numbers?

 

[Cooksey19]

Ok, that's fine, do you have any cells in col J with 0, or any very large numbers?

That was it. I did have one cell in column J that was different. But now that this works, that cell is completely unnecessary, so I just removed it. Thank you so much! Can you please also provide I note with what each line is doing? I am using this code professionally (praying they can provide VBA training soon) and i'm guessing someone will ask me to explain it. I'd prefer not to tell them it's magic lol

 

[Fluff]

Ok, glad to here it's sorted.
On the 1st time through the loop Cl will be J6, we then offset that 1 column so it becomes K6
Cl.Offset(, 1)
we then resize it by 2 columns K6:L6
Cl.Offset(, 1).Resize(, 2)

Using Range.Value=Range.Value is the same as doing a copy/paste values, except it bypasses the clipboard, making it a bit quicker.
This part

Resize(1 * Cl.Value, 2)

resizes the destination cell by 1 (the number of rows in the source range) & multiplies that by the number of times to copy, it also resizes it by 2 columns.
So with an initial destination of A6 & 3 duplicate rows this

Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1 * Cl.Value, 2)

becomes range("A6").resize(1*3,2) which is A6:B8

HTH