VBA Paste according to value in cell

[cozzagiorgi]

Hi

I need to copy stuff from one worksheet to another according to the value of another cell. E.g.

Row A says: Apples, Oranges, Bananas
Row B says: 10, 5, 7

I now need to copy Apples 10x, Oranges 5x, Bananas 7x in Row A on another worksheet.

Any ideas how I could accomplish that?

 

[My Aswer Is This]

Excel has no Row A or Row B

Excel has:

Rows 1 and Rows 2 and on and on


Excel Has Column A and Column B and Column C
And on and on.

And what is the name of the Other Worksheet and copy them where on the Other Worksheet​

 

[cozzagiorgi]

Oh yes, sorry, english is not my mother tongue, so I always forget what a row and a column is

So column A says: Apples, Oranges, Bananas
column B says: 10, 5, 7

I now need to copy Apples 10x, Oranges 5x, Bananas 7x in column A on worksheet2.

 

[My Aswer Is This]

So we want to look down column A

And every time we see a value does not have to be Apple or Orange or Banana those were just examples We want to copy that row the number of times shown in column B To sheet2
Is that correct.

 

[cozzagiorgi]

Yes

 

[My Aswer Is This]

Try this:

Sub Test()
'Modified 7/2/2018 5:10 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    For i = 2 To Lastrow
        Sheets(1).Rows(i).Copy
        Sheets(2).Rows(Lastrowa).Resize(Sheets(1).Cells(i, 2).Value).PasteSpecial
        Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[cozzagiorgi]

Hey, that works great! Thank you once again!

Wow I will have to adapt it to my use. Can you explain me how this macro works?

 

[cozzagiorgi]

I think I understand the code partially. What can i do to copy only the values in column A rather the whole rows?

 

[My Aswer Is This]

You said:

Hey, that works great! Thank you once again!

Wow I will have to adapt it to my use. Can you explain me how this macro works?​

So why do you have to adapt it to your use. Does it not do what you want?

So what might be some other ways you want to use it.

It looks down column A till if finds no more values

Copies the row and paste it that many times into sheet 2


What more do you need to know?

Resize means make larger or smaller

Resize(20) means make 20 rows longer
Resize(10,2) means make 10 rows longer and 2 columns wider

Resize(-10) means make 10 rows less

​

 

[cozzagiorgi]

My example was overly simplistic, I thought I could adapt the code myself, but looks like I am not up to that yet...

In my real world application, the values (Banana, Apples, etc) begin in range C6 with a header in C5. The numbers are in D6, also a header in D5.
I need to copy them in my Worksheet2 to a range beginning at C11. But the ranges around C11 need to remain the same.