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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Did you get the error on your test file, or your real file?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Cooksey19

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Cooksey19

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

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

Watch MrExcel Video

Forum statistics

Threads
1,114,539
Messages
5,548,631
Members
410,861
Latest member
Victor96
Top