VBA to copy number of rows to specific sheets based on a few cell values

[stupurd]

Hello.

I've search this site to try and find the answer but I what I require is a bit more specific than simply copying a list based on a quantity. I'll explain more:

I have a sheet and in this sheet lists tasks in column A, whether they are required or not in column B, The quantity in column C and finally a location (sheet) these tasks need to be copied to in column D. Hopefully, this is a bit clearer:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Task[/TD]
[TD]Required[/TD]
[TD]Qty[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]Task 1[/TD]
[TD]Y[/TD]
[TD]4[/TD]
[TD]Project Lead[/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD]Y[/TD]
[TD]4
[/TD]
[TD]Project Lead[/TD]
[/TR]
[TR]
[TD]Task 3[/TD]
[TD]N[/TD]
[TD]3[/TD]
[TD]Mechanical[/TD]
[/TR]
[TR]
[TD]Task 4[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]Project Lead[/TD]
[/TR]
[TR]
[TD]Task 5[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]Project Lead[/TD]
[/TR]
[TR]
[TD]Task 6[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]Project Lead[/TD]
[/TR]
[TR]
[TD]Task 7[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]Project Lead[/TD]
[/TR]
</tbody>[/TABLE]

I'd like the task (Cell A2), if it is required (Cell B2), to be copied the numbers of times in the Qty column (B3) into the sheet shown in the Location column (Cell B4)

The cell within each sheet that this data is to be copied into will start at Cell D3.

I hope that makes sense.

Regards

Stuart
****** id="cke_pastebin" style="position: absolute; top: 166px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]Project Lead[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[JackDanIce]

You're welcome Not much difference in the two suggested scripts, just different approaches for you to consider and adjust as need be!

 

[My Aswer Is This]

Glad you have things working for you.
I do not understand why you had to change column numbers
And I do not understand:

(The format of which is a little convoluted, a simple table was created to ask for help).​

 

[p9j123]

@my answer is this - what about if I want to copy the entire row?

Try this:
Run script from Master sheet

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

 

[My Aswer Is This]

Well what was wrong with my original answer?
You said you had to modify the columns but did not explain what was wrong.

 

[My Aswer Is This]

I thought you asked a second question about copying entire row but that question disappeared.

 

[Fluff]

I thought you asked a second question about copying entire row but that question disappeared.

You're quite the OP did.
I removed the post thinking (for some reason) that it was duplicated!
I've re-instated it, along with your reply.
Apologies for that. Must have had a "senior" moment

 

[JackDanIce]

@Fluff, think the confusion is a different poster (@p9j213) asked about the rows compared to the original poster (@stupurd) who asked about values in column A