Can you code an excel formula to copy paste a list with different copy paste amounts?

M

Milos

Board Regular
Joined
Aug 28, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have a scenario where I am trying to increase my efficiency at work because my current process usually takes ages!

I have a list that needs to be copy/pasted multiple times but in different amounts. Can this be coded somehow? I usually insert rows in between each different row and guestimate how many blank spaces there need to be minus 1. Then I perform the drag down copy method to ensure that the rows are identical.

Is there an easier way to do this!?

Fruity example: [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Contents 1[/TD]
[TD]Contents 2[/TD]
[TD]Contents 3[/TD]
[TD]Contents 4[/TD]
[TD]No. of times to be copied[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Orange[/TD]
[TD]Pear[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD]Broccoli[/TD]
[TD]Turnip[/TD]
[TD]Cabbage[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]Juice[/TD]
[TD]Milk[/TD]
[TD]Coke[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Final output wanted:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Contents 1[/TD]
[TD]Contents 2[/TD]
[TD]Contents 3[/TD]
[TD]Contents 4[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Orange[/TD]
[TD]Pear[/TD]
[TD]Banana[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Orange[/TD]
[TD]Pear[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Carrot [/TD]
[TD]Broccoli[/TD]
[TD]Turnip[/TD]
[TD]Cabbage[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD]Broccoli[/TD]
[TD]Turnip[/TD]
[TD]Cabbage[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD]Broccoli[/TD]
[TD]Turnip[/TD]
[TD]Cabbage[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]Juice[/TD]
[TD]Milk[/TD]
[TD]Coke[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]Juice[/TD]
[TD]Milk[/TD]
[TD]Coke[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]Juice[/TD]
[TD]Milk[/TD]
[TD]Coke[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]Juice[/TD]
[TD]Milk[/TD]
[TD]Coke[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Can we assume we are dealing with columns A to E ?

Can we put the results in sheet2 or must they stay in the same sheet?
And number of times is in column "E"
 
Last edited:
Upvote 0
Yes to all of above.

Yes we are dealing with columns A to E.
You can put results into any sheet that you wish including sheet 2
The number of times required to be copied is in column "E"
 
Upvote 0
You can try this:

This script requires a Sheet named: "Sheet1" and a Sheet named "Sheet2"

I have every thing working as you asked for except I cannot get column "E" to show you the results you wanted. Maybe someone else here at Mr. Excel will have a answer. Or maybe I will think of an answer later.

Code: 
Sub Resize_Me()
' MAIT 8-29-16
Application.ScreenUpdating = False
Dim i As Long
Sheets("Sheet1").Activate
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As Long
    For i = 2 To Lastrow
        Lastrowa = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet2").Range("A" & Lastrowa & ":E" & Lastrowa).Resize(Cells(i, 5).Value).Value = Range("A" & i & ":E" & i).Value
Next
End Sub
 
Upvote 0
This is how I would do it. I don't have any idea how big your actual data set is but this code, though longer than MAIT's, would be much faster if the data is big.
For the moment at least I have just put the results in the same sheet (columns J:N) but they could be put anywhere.

Rich (BB code): 
Sub Rearrang()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, c As Long
  
  With Range("A1", Range("E" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To Application.Sum(.Columns(5)), 1 To 5)
  End With
  For i = 2 To UBound(a)
    For c = 1 To a(i, 5)
      k = k + 1
      For j = 1 To 4
        b(k, j) = a(i, j)
      Next j
      b(k, 5) = c
    Next c
  Next i
  With Range("J1:N1")
    .Value = Application.Index(a, 1, 0)
    .Cells(1, 5).Value = "Number"
    .Offset(1).Resize(UBound(b)).Value = b
    .EntireColumn.AutoFit
  End With
End Sub


Data & results:


Excel 2010 32 bit
ABCDEFGHIJKLMN
1Contents 1Contents 2Contents 3Contents 4No. of times to be copiedContents 1Contents 2Contents 3Contents 4Number
2AppleOrangePearBanana2AppleOrangePearBanana1
3CarrotBroccoliTurnipCabbage3AppleOrangePearBanana2
4WaterJuiceMilkCoke4CarrotBroccoliTurnipCabbage1
5CarrotBroccoliTurnipCabbage2
6CarrotBroccoliTurnipCabbage3
7WaterJuiceMilkCoke1
8WaterJuiceMilkCoke2
9WaterJuiceMilkCoke3
10WaterJuiceMilkCoke4
11
Sheet1
 
Upvote 0
Thanks guys! That is epic work!

I do have a relatively large dataset approximately 18 columns across (A:R) and approximately 50 rows down (1:50). I am an absolute rookie at coding at this stage.. so could you please spoon-feed me the changes that I would need to make to make the macro work? The number rows (column E; column N in this example) are always at the end of my data set.
 
Upvote 0
You said:
I do have a relatively large dataset

A dataset of only 50 rows is not a large dataset. Some people here are using a million rows now that is large.
In post #3 you said your data was in columns A to E but in post #7 you said:

The number rows (column E; column N in this example

Maybe Peter can help you but I'm not clear where your data is.
Specific details in your original post always speeds up getting answers to your questions.
 
Upvote 0
Millions... I am glad I have a small dataset. Okay I will make my question this fully understandable with more tables, everybody loves tables.

So instead of the table size being from A-E (as in the previous example) it is now A-S:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"] S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Contents 1[/TD]
[TD]Contents 2[/TD]
[TD]Contents 3[/TD]
[TD]C4[/TD]
[TD]C5[/TD]
[TD]C6[/TD]
[TD]C7[/TD]
[TD]C8[/TD]
[TD]C9[/TD]
[TD]C10[/TD]
[TD]C11[/TD]
[TD]C12[/TD]
[TD]C13[/TD]
[TD]C14[/TD]
[TD]C15[/TD]
[TD]C16[/TD]
[TD]C17[/TD]
[TD]Contents 18[/TD]
[TD]No. of copies needed[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]Cherry[/TD]
[TD]Date[/TD]
[TD]Ee[/TD]
[TD]eF[/TD]
[TD]Gee[/TD]
[TD]acHe[/TD]
[TD]Ii[/TD]
[TD]Jay[/TD]
[TD]Kay[/TD]
[TD]eL[/TD]
[TD]eM[/TD]
[TD]eN[/TD]
[TD]Oo[/TD]
[TD]Pee[/TD]
[TD]Qew[/TD]
[TD]Raisin[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Asparagus[/TD]
[TD]Brocolli[/TD]
[TD]Cucumber[/TD]
[TD]D[/TD]
[TD]Ee[/TD]
[TD]eF[/TD]
[TD]Gee[/TD]
[TD]acHe[/TD]
[TD]Ii[/TD]
[TD]Jay[/TD]
[TD]Kay[/TD]
[TD]eL[/TD]
[TD]eM[/TD]
[TD]eN[/TD]
[TD]Oo[/TD]
[TD]Pee[/TD]
[TD]Qew[/TD]
[TD]Raddish[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Agua[/TD]
[TD]Beer[/TD]
[TD]Carrot Juice[/TD]
[TD]Diet coke[/TD]
[TD]Ee[/TD]
[TD]eF[/TD]
[TD]Gee[/TD]
[TD]acHe[/TD]
[TD]Ii[/TD]
[TD]Jay[/TD]
[TD]Kay[/TD]
[TD]eL[/TD]
[TD]eM[/TD]
[TD]eN[/TD]
[TD]Oo[/TD]
[TD]Pee[/TD]
[TD]Qew[/TD]
[TD]Rum[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

The final output needed would be the same concept as what you guys have already designed. BUT I was too useless to work out how to change the parameters to suit my need.
 
Upvote 0
This slight modification of my earlier code should work for data of any size, provided ..
- The data starts at cell A1
- The number of copies is always in the right-most column of the block of data
- There are no completely blank rows or columns within the data to be rearranged

This time I have put the results below the original data.

Rich (BB code): 
Sub Rearrang_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, c As Long, cols As Long
  
  With Range("A1").CurrentRegion
    a = .Value
    cols = .Columns.Count
    ReDim b(1 To Application.Sum(.Columns(cols)), 1 To cols)
  End With
  For i = 2 To UBound(a)
    For c = 1 To a(i, cols)
      k = k + 1
      For j = 1 To cols - 1
        b(k, j) = a(i, j)
      Next j
      b(k, cols) = c
    Next c
  Next i
  With Range("A1").Offset(UBound(a) + 2).Resize(, cols)
    .Value = Application.Index(a, 1, 0)
    .Cells(1, cols).Value = "Number"
    .Offset(1).Resize(UBound(b)).Value = b
    .EntireColumn.AutoFit
  End With
End Sub

For original data in A1:S4, the code produced the table in rows 7:20


Excel 2010 32 bit
ABCDEFGHIJKLMNOPQRS
1Contents 1Contents 2Contents 3C4C5C6C7C8C9C10C11C12C13C14C15C16C17Contents 18No. of copies needed
2AppleBananaCherryDateEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaisin5
3AsparagusBrocolliCucumberDEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaddish6
4AguaBeerCarrot JuiceDiet cokeEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRum2
5
6
7Contents 1Contents 2Contents 3C4C5C6C7C8C9C10C11C12C13C14C15C16C17Contents 18Number
8AppleBananaCherryDateEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaisin1
9AppleBananaCherryDateEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaisin2
10AppleBananaCherryDateEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaisin3
11AppleBananaCherryDateEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaisin4
12AppleBananaCherryDateEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaisin5
13AsparagusBrocolliCucumberDEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaddish1
14AsparagusBrocolliCucumberDEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaddish2
15AsparagusBrocolliCucumberDEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaddish3
16AsparagusBrocolliCucumberDEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaddish4
17AsparagusBrocolliCucumberDEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaddish5
18AsparagusBrocolliCucumberDEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRaddish6
19AguaBeerCarrot JuiceDiet cokeEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRum1
20AguaBeerCarrot JuiceDiet cokeEeeFGeeacHeIiJayKayeLeMeNOoPeeQewRum2
Sheet3
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Formula/Math question (weights)
Replies
3
Views
270
felixstraube
F
L
  • Question Question
Selection based on two variables - copy and paste to another sheet
Replies
3
Views
451
kvsrinivasamurthy
K
T
  • Solved
Match with multiple columns
2
Replies
14
Views
823
Peter_SSs
Peter_SSs
E
  • Solved
Using Excel to copy and paste one of 9 different replies
Replies
6
Views
311
ellison
E
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
780
myall_blues
myall_blues

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top