Nested for Loop

[Arae97]

I can't seem to figure out how to jump to the next "c" in the loop after pasting my data. it works the first time but does not return to the first loop to change the c value to continue the process. any help would be great

Sub CopyPasteData()
Dim Pool As Worksheet
Dim Family As Range
Dim i As Integer
Dim Finalrow As Integer
Dim c As Variant
Set Pool = Sheet1
Set Family = Pool.Range("R2:R16")
Pool.Select
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row


For Each c In Family


For i = 2 To Finalrow
If Cells(i, 1) = c.Value Then
Range(Cells(i, 2), Cells(i, 13)).Copy
Sheets(c.Value).Select
Range("a3").End(xlToRight).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Pool.Select
End If
Exit For

Next c


End Sub
[/code]

 

[njimack]

Not sure if this will solve your problem but c should be a Range, not Variant

 

[Arae97]

yes I tried both thinking it may be a problem. thanks for your help but there is still a problem.

 

[Fluff]

You're missing a Next i

 

[Arae97]

Sub CopyPasteData()
Dim Pool As Worksheet
Dim Family As Range
Dim i As Integer
Dim Finalrow As Integer
Dim c As Range
Set Pool = Sheet1
Set Family = Pool.Range("R2:R16")
Pool.Select
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row


For Each c In Family


For i = 2 To Finalrow
If Cells(i, 1) = c.Value Then
Range(Cells(i, 2), Cells(i, 13)).Copy
Sheets(c.Value).Select
Range("a3").End(xlToRight).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Pool.Select
End If
Next i
Next c


End Sub

This is what i have changed to it. but it still doesn't change to the next c. it continues through the second loop not starting back at the first.

 

[Fluff]

Can you please explain what you mean by

it continues through the second loop not starting back at the first.

 

[Arae97]

when i step into the query to find the problem it doesn't change the c value to the next value in the range it remains the same.

 

[Fluff]

If you add this msgbox box

For Each c In Family
MsgBox c.Value

For i = 2 To Finalrow

Does the value change each time it opens?

 

[Steve_]

I feel like this is what you are looking for...

Sub CopyPasteData()
Dim i As Integer, c As Variant
    ThisWorkbook.Sheets("Sheet1").Activate
    For Each c In Range("R2:R16")
        For i = 2 To ThisWorkbook.Sheets("Sheet1").UsedRange.Rows.Count
            If Cells(i, 1) = c.Value Then
                Range(Cells(i, 2), Cells(i, 13)).Copy
                Sheets(c.Value).Select
                Range("a3").End(xlToRight).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
                ThisWorkbook.Sheets("Sheet1").Activate
                Exit For
            End If
        Next i
    Next c
End Sub

 

[Arae97]

no it did not change. it only showed the box once for the first value. when I step into it to run step by step it it never returns up past the "For i = 2 To Finalrow"