Excel VBA: Loop within Loop to copy from one sheet & paste into another using IF statement

[ExcelR00ki3]

Hi Excel/VBA Superusers!

Hoping someone out there might be able to help me.

I've been trawling through the forums hoping to find some help on how to write my VBA code (i've not written code for over a year ) but have had no luck finding anything that resembles what I'm trying to do.

Here is an example of my data (this is only a snippet. The actual table has the potential to be over 1000 rows long):

[table="width: 500, class: grid"]
[tr]
[td]Title[/td]
[td]APN[/td]
[td]Price[/td]
[td]Ranged[/td]
[/tr]
[tr]
[td]Test 1[/td]
[td]9000000000000[/td]
[td]9.95[/td]
[td]Y[/td]
[/tr]
[tr]
[td]Test 2[/td]
[td]9000000000001[/td]
[td]10.00[/td]
[td]N[/td]
[/tr]
[tr]
[td]Test 3[/td]
[td]9000000000002[/td]
[td]10.95[/td]
[td]Y[/td]
[/tr]
[tr]
[td]Test 4[/td]
[td]900000000003[/td]
[td]12.00[/td]
[td]N[/td]
[/tr]
[/table]


Basically, what I need the code to do is in Sheet1, go through the "Ranged" column, and for everytime a "Y" appears, copy the corresponding "Title" and paste it into a new table in Sheet2.

ie my expected output would be:

[table="width: 100, class: grid"]
[tr]
[td]TITLE[/td]
[/tr]
[tr]
[td]Test 1[/td]
[/tr]
[tr]
[td]Test 3[/td]
[/tr]
[/table]

Ideally, I'd copy the whole row but I thought that if I can just get the titles, I can vlookup/index, match the rest of the information in the actual spreadsheet. I know that this would contain a loop within a loop (potentially 3?) and would be based on an IF statement, but for the life of me, I can't think of how this would work.

If you could take the time to help, it would be very much appreciated.

Thanks
Kate

 

[My Aswer Is This]

Try this:
This script assumes your "Y" is in column (4)

Sub Loop_within_Loop()
Application.ScreenUpdating = False
'http://www.mrexcel.com/forum/excel-questions/899910-excel-visual-basic-applications-loop-within-loop-copy-one-sheet-paste-into-another-using-if-statement.html#post4334031
Dim i As Integer
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(1).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1

    For i = 1 To Lastrow
        If Cells(i, 4).Value = "Y" Then
            Sheets(2).Rows(Lastrowa).Value = Rows(i).Value
            Lastrowa = Lastrowa + 1
        End If
    Next

Application.ScreenUpdating = True
End Sub

 

[ExcelR00ki3]

Try this:
This script assumes your "Y" is in column (4)

Sub Loop_within_Loop()
Application.ScreenUpdating = False
'http://www.mrexcel.com/forum/excel-questions/899910-excel-visual-basic-applications-loop-within-loop-copy-one-sheet-paste-into-another-using-if-statement.html#post4334031
Dim i As Integer
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(1).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1

    For i = 1 To Lastrow
        If Cells(i, 4).Value = "Y" Then
            Sheets(2).Rows(Lastrowa).Value = Rows(i).Value
            Lastrowa = Lastrowa + 1
        End If
    Next

Application.ScreenUpdating = True
End Sub

Brilliant! Works a treat! Thanks so much @My Aswer Is This!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Brilliant! Works a treat! Thanks so much @My Aswer Is This!

 

[ExcelR00ki3]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Sorry! Quick question. I've changed the formula to suit my spreadsheet (ie. I have had to duplicate it across several new worksheets) but am finding that as it is copying the whole row, it is also copying the "Y" which I don't need. Are you please able to tell me where I need to specify the cells to copy? Eg. I would only need to copy columns 1-3.

Thanks again!
Kate

 

[My Aswer Is This]

The reason I wrote the script this way is because you said Quote:
"Ideally, I'd copy the whole row but I thought that if I "
I'll work on changing it.
And what does this mean:
I've changed the formula to suit my spreadsheet (ie. I have had to duplicate it across several new worksheets)
If you want it to work on several sheets you could have mentioned that and I could have written the script to do that.

 

[ExcelR00ki3]

The reason I wrote the script this way is because you said Quote:
"Ideally, I'd copy the whole row but I thought that if I "
I'll work on changing it.
And what does this mean:
I've changed the formula to suit my spreadsheet (ie. I have had to duplicate it across several new worksheets)
If you want it to work on several sheets you could have mentioned that and I could have written the script to do that.

Apologies, it was not my intention to be difficult.

I was just hoping that if I could get started with someone else's code, I might be able to amend it myself to suit. I wanted to try and do things myself also as I will have to populate at least another 3 worksheets (this number may grow in future) so thought I would copy and paste the code and amend for the new worksheets - which did work.

Essentially, I have a master sheet which will feed into different worksheets based on whether or not there is a "Y" in the column. Eg. From my initial table of data, the "Ranged" column would be duplicated for different retailers and so a new worksheet would be populated with data from the Master based on this. Also, I should mention that my data table in the Master sheet doesn't actually start at A1 but rather A15 (there is another table above which is informational, but the data is not used for any outputs).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Title[/TD]
[TD]APN[/TD]
[TD]Price[/TD]
[TD]Ranged A[/TD]
[TD]Ranged B[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]9000000000000[/TD]
[TD]9.95[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]9000000000001[/TD]
[TD]10.00[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]

I would thus be copying data from columns A-C (title, APN, Price).

Sheet "Ranged A" would then have the result

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Title[/TD]
[TD]APN[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]9000000000000[/TD]
[TD]9.95[/TD]
[/TR]
</tbody>[/TABLE]

Where Sheet "Ranged B" would have the result

[TABLE="width: 300"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Title[/TD]
[TD]APN[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]9000000000001[/TD]
[TD]10.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully, this makes sense. I sincerely apologise if this has caused any inconvenience.

 

[My Aswer Is This]

Try this:
It will now only copy over columns A,B and C data
And I have it starting on Row(15)

Sub Loop_within_Loop()
'Version 2
Application.ScreenUpdating = False
'http://www.mrexcel.com/forum/excel-questions/899910-excel-visual-basic-applications-loop-within-loop-copy-one-sheet-paste-into-another-using-if-statement.html#post4334031
Dim i As Integer
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(1).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1

    For i = [COLOR="#FF0000"]15[/COLOR] To Lastrow 'I changed this to 15 the 15 is the first row it will start on
        If Cells(i, 4).Value = "Y" Then
            Range(Cells(i, 1), Cells(i, 3)).Copy Destination:=Sheets(2).Cells(Lastrowa, 1)
            Lastrowa = Lastrowa + 1
        End If
    Next

Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

If I could invoke a suggestion.
I believe I understand what your attempting to do with additional sheets.
Instead of continuing to add more columns to accommodate more sheets why not do this.
If you want this rows data to go to sheet named “Ranged A” why not put the text “Range A” into column 4 instead of “Y” now the script would look on every row beginning with row (15) and it looks in column (4) and copies that rows data to the sheet name put in column (4).
You can then add all the sheets you want and never add more column and never need to change the script.
You always run the script from the master sheet and it should all work out for you unless there is more I do not understand.
The folks here at Mr. Excel enjoy helping people like you it’s just hard sometimes understanding what the user is wanting.
Let me know and I can modify the script to do this. This change would only take me about 10 minutes to make.

 

[ExcelR00ki3]

If I could invoke a suggestion.
I believe I understand what your attempting to do with additional sheets.
Instead of continuing to add more columns to accommodate more sheets why not do this.
If you want this rows data to go to sheet named “Ranged A” why not put the text “Range A” into column 4 instead of “Y” now the script would look on every row beginning with row (15) and it looks in column (4) and copies that rows data to the sheet name put in column (4).
You can then add all the sheets you want and never add more column and never need to change the script.
You always run the script from the master sheet and it should all work out for you unless there is more I do not understand.
The folks here at Mr. Excel enjoy helping people like you it’s just hard sometimes understanding what the user is wanting.
Let me know and I can modify the script to do this. This change would only take me about 10 minutes to make.

Apologies for the delayed response - I try to stay away from doing work over the weekend.

Thank you also for your suggestion. It makes perfect sense and I will talk the guys here working with it. If it's not too much trouble, I would really appreciate if you could update the code.

Thanks so much for all of your help @My Aswer is This!